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 options argument offers more choices for which rows are included.

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,
.

This article is an excerpt from Power Excel With MrExcel
Title photo by Daniel Lerman on Unsplash