Can You Get Medians?

October 06, 2022 - by Bill Jelen

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.

The AGGREGATE function offers the same 11 function numbers as SUBTOTAL, with eight extra functions:
12 Median
13 Mode.SNGL
14 Large
15 Small
16 Percentile.Inc
17 Percentile.Exc
19 Quartile.Exc — Figure 738. Aggregate offers more calculation options.

The options argument offers more choices for which rows are included.

Continuing with the AGGREGATE function, the Options argument offers 8 choices:
0 Ignore nested Subtotal and Aggregate Function (this phrase is shortened to "Nested" in choices 1, 2, and 3.
1 Ignore hidden rows and Nested
2 Ignore error values, plus Nested
3 Ignore errors, hidden, and Nested
4 Ignore Nothing
5 Ignore Hidden
6 Ignore Errors
7 Ignore Hidden and Errors — Figure 739. 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,.

In the Find and Replace dialog, Find SUBTOTAL(9, and replace with AGGREGATE(12,0, Replace All — Figure 740. Change subtotals to medians.

This article is an excerpt from Power Excel With MrExcel

Title photo by Daniel Lerman on Unsplash