pivot: average vs. median

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Beautiful !

thanks.

Conrad Carlberg said:
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
Click to expand...
 
I know it's an old post, but just found it and it is great, especially since
my medians need to be in a row above the pivot table. Just wish I had seen
this a few months ago, but better now than never. Thank you.
 
Back
Top