Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
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" wrote: 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)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
I just must be doing something wrong because the 2nd and 3rd rows returned a
value but the rest are zero. "Max" wrote: 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" wrote: 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)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median Calculation
"SB Lee" wrote:
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median calculation of grouped data. | Excel Worksheet Functions | |||
median calculation | Excel Discussion (Misc queries) | |||
Median | Excel Discussion (Misc queries) | |||
Max/Min/Median | Excel Worksheet Functions | |||
Median calculation and ignore zeros | Excel Discussion (Misc queries) |