Menu
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?
458
Here is a simple formula can do you a favor on calculating median value. Select a cell which you will place the median value, type this formula =AGGREGATE(12,1,A2:A17) into it, then press Enter key.
Supposing you have filtered your table as following screen shots shown, you can get its average as follows: In a blank cell enter the formula =SUBTOTAL(1,C12:C24) (C12:C24 is the range with all filtered data in Amount column) and press the Enter key.
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 shiftctrlenter
=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)
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
