Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal the median
I would like to set up a subtotal that generates the median of a range of
numbers (with blanks not included) Example: Title Salary Director Information Technology $85,000 Director Information Technology $95,000 Director Information Technology $105,000 Director Information Technology Director Human Resources $65,000 Director Human Resources $75,000 Director Human Resources $85,000 Director Human Resources $95,000 Director Human Resources $105,000 Director Human Resources I will be subtotalling "at the change" in each Title and counting how many Salaries were reported (3 total count for Director Information Technology and 5 total count for Director Human Resources ). Then I want to subtotal "at the change" in each Title and generate the median Salary. Director Information Technology Median = $95,000 Director Human Resources Median = $85,000 Anyone know how to write this? Ultimately I would like to drop the custom formula into a pivot table. Thank you in advance - Terri |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal the median
I'd apply the Count function to both the title and salary. Then select the
Salary column and perform a find/replace, changing "SUBTOTAL(3," to "MEDIAN(". (Alternately, I might stick with the count on the salary field, then filter so that only the subtotals are shown and use the offset function with the median function.) --Bruce "Terri" wrote: I would like to set up a subtotal that generates the median of a range of numbers (with blanks not included) Example: Title Salary Director Information Technology $85,000 Director Information Technology $95,000 Director Information Technology $105,000 Director Information Technology Director Human Resources $65,000 Director Human Resources $75,000 Director Human Resources $85,000 Director Human Resources $95,000 Director Human Resources $105,000 Director Human Resources I will be subtotalling "at the change" in each Title and counting how many Salaries were reported (3 total count for Director Information Technology and 5 total count for Director Human Resources ). Then I want to subtotal "at the change" in each Title and generate the median Salary. Director Information Technology Median = $95,000 Director Human Resources Median = $85,000 Anyone know how to write this? Ultimately I would like to drop the custom formula into a pivot table. Thank you in advance - Terri |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
subtotal - pivot table - or better function | Excel Worksheet Functions | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Excel's Pivot Table & Subtotal function should have a median fie. | Excel Worksheet Functions |