Median Calculation

[SB Lee]

I am summarizing a large amount of compensation data using pivot reports and
need to include the median. I do not think median is available for use in
pivot tables or when even just subtotaling data - what would be the best way
to quickly find medians for many different groupings in a large amount of
data?

 

[Max]

One way to get at it is via using conditional, array-entered* expressions
like this:
=MEDIAN(IF((A$2:A$10="Dept1")*(B$2:B$10="Assoc"),C$2:C$10))
*press CTRL+SHIFT+ENTER to confirm the formula(not just ENTER)

The above derives the median salary of staff of rank: Assoc in Dept1,
assuming col C houses the salary data. You can point to cells housing the
various params for dept and rank (eg params in 2 cols) instead of it being
hardcoded in the example, and hence easily propagate the expression down to
derive all the various results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[SB Lee]

Thank you. The piece I am not clear on is how to create the formula without
hard coding the params. The data I need to use to identify the median is in
2 columns - column A has job title and column B has salary - there are
approximately 1000 different job titles that I need to find the medians for.

 

[Max]

Assume job titles and salary data are in Sheet1's cols A and C, in row2 down.
Do a quick pivot just on that job titles col, place the col header in the ROW
and DATA area (set to count). The pivot will return the list of all the
unique job titles and its corresponding counts in a new sheet. In that pivot
sheet, assume the unique job titles are listed in A5 down. Use an adjacent
col outside the pivot, say C5,
place this and array-enter:
=MEDIAN(IF(Sheet1!A$2:A$5000=A5,Sheet1!C$2:C$5000))
Copy down to return the required results for each unique job title
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[SB Lee]

I created the pivot table as you stated and am using the following formula
but it is just bringing back o's. =MEDIAN(IF('Census by
Title'!E$2:E$3251=A5,'Census by Title'!Q$2:Q$3251))

 

[Max]

It should have worked. Probably your source data is not cleansed, and the
source col Q may contain text nums. Try this heavier duty adaptation of your
expression which takes care of all possibilities, array-entered:
=MEDIAN(IF(TRIM('Census by Title'!E$2:E$3251)=TRIM(A5),'Census by
Title'!Q$2:Q$3251+0))

Success, finally? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[SB Lee]

I just must be doing something wrong because the 2nd and 3rd rows returned a
value but the rest are zero.

 

[Max]

I just must be doing something wrong because the 2nd and 3rd rows returned a
value but the rest are zero.

I don't know. The expressions given should work fine. You'd have to re-check
carefully your source data, eg in col Q there could be some non-numerics
(text, not text nums), residual errors? etc. Use the autofilter droplist on
col Q to quick-screen through the list and rectify.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---