This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Can You Get Medians?
Problem: Why doesn’t the subtotal feature offer Median?
Strategy: In Excel 2010, Microsoft added a new function called AGGREGATE. The AGGREGATE function is SUBTOTALS’s stronger cousin. The function offers the same 11 calculation options plus several new ones.
Figure 679 Aggregate offers more calculation options.
The options argument offers more choices for which rows are included.
Figure 680 More options for what to ignore.
The AGGREGATE function offers potential for some incredible calculations. The new calculation arguments of 12 through 19 allow for array formulas, which would lead to some good additions for the Excel Gurus Gone Wild book. But to solve the median problem, it requires a simple Find and Replace.
To use a MEDIAN in a subtotal, you can use the Subtotal command to sum the column in question.
Select the column. Use Find and Replace. Find SUBTOTAL(9, and replace with AGGREGATE(12,0,.