How do you figure out the MEDIAN of a column taking into account filters?

excel excel-formula excel-2010
Diskdrive picture Diskdrive · Aug 28, 2012 · Viewed 29.5k times · Source

I've recently discovered that you can use SUBTOTAL for various functions which allow you to sum up or find totals of a column, even whilst there are filters being applied to it.

However, the list of functions SUBTOTAL has does not include MEDIAN.

Is it possible to find the median of a column of numbers taking into that some rows have filtered out?

Answer

brettdj picture brettdj · Aug 28, 2012

Updated to pick up comments from lori_m below

1. Original answer - all xl versions
Courtesy of Aladin Akyurek from this solution If your data was in A1:A10, array enter this formula with =MEDIAN(IF(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)),A1:A10))

2. Updated answer (non-array) - all xl versions =MEDIAN(IF(SUBTOTAL(3,OFFSET(A1:A10,MMULT(ROW(A1:A10),1)-MIN(MMULT(ROW(A1:A10),1)),,1)),A1:A10))

3. Excel 2010 & Excel 2013
=AGGREGATE(12,5,A1:A10)

Related questions

Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Ignore Duplicates and Create New List of Unique Values in Excel

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows: Column A Column B a a a b b c c c …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More