Excel - Using the Median function with Auto-filter

Asked By BASFMark on 03-Feb-10 05:58 PM
How do I get median (=median(A1:A50000) to show a result for only filtered
cells?  When I autofilter on a different column and the number of visible
cells is reduced by the filter, the median stays the same as the unfiltered


Bernie Deitrick replied to BASFMark on 03-Feb-10 10:33 PM

You could use a column of helper cells.

In B1 enter


and copy down to match column A.

Then array enter the formula (enter using Ctrl-Shift-Enter)


Filtering column A will change the result of the SUBTOTAL functions, and
thus the result of the array-entered MEDIAN function.

MS Excel MVP