Median with pivot table roll-up - Excel Medians

DigDB

Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Median - Roll-up (pivot table) with Medians

Excel has a median formula, but it does NOT support median in Pivot Table or Subtotal. Pivot table and subtotal only has the Average, a.k.a Mean.

The Median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less. If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. For example:

MEDIAN of the set of numbers (1, 2, 3, 4, 5) equals 3
MEDIAN of the set of numbers (1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4

Below is an Employee list table, suppose we want to find out the median employee 'Age' of each 'Department'.

Practice file - median-pivot-table-demo.xls (16k)

Click any cell within your list area, Invoke 'DigDB->Roll up...'
Set summary criteria: find the middle (Median) Age value in each Department
- Use the Calculations area to select 'Median' of 'Age', use the upper 'Add' button to add to the criteria box
- Use the Group-by(s) area to select 'Department', then use the lower 'Add' button to add to the criteria box
Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

Besides Median, DigDB's roll up also supports Count of Unique values, MODE, Standard Deviation and a total of 21 statistical functions. This give users far more aggregate power than Pivot table and Subtotal.

Need more?

Browse the complete list of DigDB's powerful features listed on the front page.
If you see errors or things not working, check out our error reference page.

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.