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

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