Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table calculated field
I have created a pivot table to display salary information for employees
across several departments and job titles. From the salary column I have created several data fields: Count of Salaries, Avg Salary, Max Salary, Min Salary. I would like to create several addtional fields as follows: Median Salary, 25th percentile, 50th percentile, and 75th percentile. I have been unable to create Calculated fields to display these values. Any idea as to how I can do this? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table calculated field
Hi Neil,
Bad news - you can't do this in the pivot table. Why? 1. As you have discovered Excel has only 11 built in functions for summarizing data in the pivot table. 2. Even though you can create a calculated field and use the MEDIAN function with it, the results are meaningless because you can only apply the MEDIAN after Excel has summarized the data, and to calculate this statistic you need to have access to the raw data level within the cache, and we don't. You might create the calculations outside the pivot table -- Thanks, Shane Devenshire "Neil T" wrote: I have created a pivot table to display salary information for employees across several departments and job titles. From the salary column I have created several data fields: Count of Salaries, Avg Salary, Max Salary, Min Salary. I would like to create several addtional fields as follows: Median Salary, 25th percentile, 50th percentile, and 75th percentile. I have been unable to create Calculated fields to display these values. Any idea as to how I can do this? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table calculated field
I was afraid of that. Thanks, Shane. I appreciate the help!
-- Neil T "ShaneDevenshire" wrote: Hi Neil, Bad news - you can't do this in the pivot table. Why? 1. As you have discovered Excel has only 11 built in functions for summarizing data in the pivot table. 2. Even though you can create a calculated field and use the MEDIAN function with it, the results are meaningless because you can only apply the MEDIAN after Excel has summarized the data, and to calculate this statistic you need to have access to the raw data level within the cache, and we don't. You might create the calculations outside the pivot table -- Thanks, Shane Devenshire "Neil T" wrote: I have created a pivot table to display salary information for employees across several departments and job titles. From the salary column I have created several data fields: Count of Salaries, Avg Salary, Max Salary, Min Salary. I would like to create several addtional fields as follows: Median Salary, 25th percentile, 50th percentile, and 75th percentile. I have been unable to create Calculated fields to display these values. Any idea as to how I can do this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table calculated field | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table | Excel Worksheet Functions | |||
Pivot Table Calculated Field- Help | Excel Worksheet Functions |