Median in pivot tables

[SB Lee]

By using pivot tables, I am showing statistical data for base pay for several
hundred different jobs and need to add statistics for the median. How can i
add the median to the pivot table?

Thanks.

 

[Debra Dalgleish]

The Median function isn't available to summarize Pivot Table fields.

 

[SB Lee]

Is there another way to easily show a list of medians for the applicable jobs
and then incorporate it in to the pivot table or at least show it as a stand
alone statistic?

 

[Shane Devenshire]

Hi,

Yes, you may be able to do it by incorpating the calculation into your data
area for example:

Suppose you have the Job in column A and the Base Pay in column B of your
source range, then you can add the following array formula to column C of the
data:

=MEDIAN(IF(A2:A1000=A4,B2:B1000,""))

to make this an array you press Shift+Ctrl+Enter to enter it. Copy this all
the way down to the bottom of your data. Title this column Medium in C1.

Now create a pivot table and put the Job in the row area and the Median into
the Data area. Finally, change the calculation for the Median from Sum to
Average.

Now the down side of this approach is that you can't rearrange your pivot
table and expect to get reasonable results.

A second alternative is to do it all by forrmulas outside the pivot table
but referencing the pivot table. The basic idea is to use a formula like the
one above or a more complex variation of it.

More detail and I could help you more.

 

[Max]

Thought we discussed all of these comprehensively
in your earlier thread?

http://tinyurl.com/lq6em9

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Median
http://www.mediafire.com/file/rurdzayt41i/06_09_09a.xlsx