pivot: average vs. median

I'd like to use a pivot table and get a median value rather than an average.
Any formula or other way I could do this? Percentile formulas perhaps?

The outliers in my data make the average look much too high

thanks,
dave

Hi Dave,

I've never found a way to get a pivot table to calculate a median directly.
You might find this workaround useful, though.

Suppose that your pivot table is based on data in A1:B7, such that column A
contains the values for your pivot table's row field and column B contains
the values for the data field. The field labels are in row 1.

In the pivot table itself, one row field value is in cell A12 and another is
in cell A13. The pivot table occupies A10:B14 (there's a Grand Total row).
In cell C12, I array-enter this formula:

=MEDIAN((IF(A12=$A$2:$A$7,$B$2:$B$7, "")))

(You array-enter a formula by first typing it, then by holding down
Ctrl-Shift as you press Enter.)

Then I copy and paste the formula into cell C13 -- or, with many more unique
row field values, as far down as required to pick up each unique row value
in the table. All that this buys you is the ability to find the data field
values associated with each row field value and get their median, and to
make use of the Refresh Data command to update those medians as needed. I'd
use dynamic range names, BTW, instead of absolute range addresses in the
MEDIAN formulas and the pivot table's source definition. This, so that the
pivot table can refresh itself accurately as you add new data, without
requiring you to redefine the data source's address. If you do that, don't
put your pivot table in the same columns as its data source.

C^2
Conrad Carlberg

--
Excel Sales Forecasting for Dummies, Wiley, 2005

"dave in Toronto" <dave in [email protected]> wrote in
message news:[email protected]...
> I'd like to use a pivot table and get a median value rather than an
average.
> Any formula or other way I could do this? Percentile formulas perhaps?
>
> The outliers in my data make the average look much too high
>
> thanks,
> dave

Beautiful !

thanks.

"Conrad Carlberg" wrote:

> Hi Dave,
>
> I've never found a way to get a pivot table to calculate a median directly.
> You might find this workaround useful, though.
>
> Suppose that your pivot table is based on data in A1:B7, such that column A
> contains the values for your pivot table's row field and column B contains
> the values for the data field. The field labels are in row 1.
>
> In the pivot table itself, one row field value is in cell A12 and another is
> in cell A13. The pivot table occupies A10:B14 (there's a Grand Total row).
> In cell C12, I array-enter this formula:
>
> =MEDIAN((IF(A12=$A$2:$A$7,$B$2:$B$7, "")))
>
> (You array-enter a formula by first typing it, then by holding down
> Ctrl-Shift as you press Enter.)
>
> Then I copy and paste the formula into cell C13 -- or, with many more unique
> row field values, as far down as required to pick up each unique row value
> in the table. All that this buys you is the ability to find the data field
> values associated with each row field value and get their median, and to
> make use of the Refresh Data command to update those medians as needed. I'd
> use dynamic range names, BTW, instead of absolute range addresses in the
> MEDIAN formulas and the pivot table's source definition. This, so that the
> pivot table can refresh itself accurately as you add new data, without
> requiring you to redefine the data source's address. If you do that, don't
> put your pivot table in the same columns as its data source.
>
> C^2
> Conrad Carlberg
>
> --
> Excel Sales Forecasting for Dummies, Wiley, 2005
>
> "dave in Toronto" <dave in [email protected]> wrote in
> message news:[email protected]...
> > I'd like to use a pivot table and get a median value rather than an
> average.
> > Any formula or other way I could do this? Percentile formulas perhaps?
> >
> > The outliers in my data make the average look much too high
> >
> > thanks,
> > dave
>
>
>