Pivot Table Question - Outliers, Medians and how to remove

[DimPrawn]

Yep. Thought this would be too complex for the members.

Or it could be we aren't cocking about with beginners toys like Excel. Ask sasguru, he's an excel jockey.

[doodab]

Yep. Thought this would be too complex for the members.

Do you feel it's a good use of our employers time to help you with basic spreadsheet questions?

[MarillionFan]

Or you could try doing your job yourself

I've done it myself. I'll publish the result on a proper technical forum somewhere.

[Spacecadet]

Yep. Thought this would be too complex for the members.

Or you could try doing your job yourself

[MarillionFan]

Yep. Thought this would be too complex for the members.

[MarillionFan]

I've got a large data set in Excel(200k rows) which I am using a pivot table for reporting on.

The data is grouped Dimension 1, Dimension 2 and then by Month. One measure is count, one measure is average.

On looking at the trended data the averages are not consistent, on investigation due to outliers.

I would like to add in a Median. Pivot tables do not use Median or mode.

As well as a Median I would like to add in an adjusted average. Ie. I would like to remove the outliers at 2.5% on either side, giving me an 'adjusted' average.

Any bright ideas without having to resorting to messing about with the raw data before putting it in the Pivot?

Brucie bonus points available.