Excel: 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.

1. Aggregate offers more calculation options.

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

   

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

   

3. Change subtotals to medians.

   Additional Details: If you are interested in Medians, you might also want to create a Median in a pivot table. The Power Pivot add-in for Excel 2015 will finally allow medians in pivot tables. As I write this, the feature is in beta, but as you read this, it might be available.Horizontal Subtotals

   Problem: Why doesn't Excel offer horizontal subtotals?

   

4. Add a subtotal in E for Q1.

   Strategy: This is a great question. In my podcast episode 1001, I had several people write in to say that they regularly used this method to add horizontal subtotals. Although it is a lot of steps, if you use shortcut keys, it is actually fast.

   1. Select the original data with Ctrl+*
   2. Go a few rows below the data. Paste with Alt+E+S+E+Enter.
   3. Alt+D+B to display the Subtotals dialog. Click OK.
   4. Ctrl+C to copy the vertical data set with the subtotals.
   5. Select cell A1.
   6. Paste Transpose with Alt+E+S+E+Enter.
   7. Fix the column widths with Alt+O+C+A.

   

5. Horizontal subtotals.
   8. Delete the temporary table at the bottom.
   9. Optionally, select columns B:D and choose Data, Group.
   10. Select columns F:H and press F4 to re-do the group command.
   11. Repeat step 10 for J:L and N:P.
   12. Select B:Q and choose Data, Group.

   You now have collapsible horizontal subtotals.

   

6. After manually adding groups.