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

Thanks!




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

You could use a column of helper cells.

In B1 enter

=IF(SUBTOTAL(3,A1)=0,"",A1)

and copy down to match column A.

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

=MEDIAN(IF(B1:B50000<>"",B1:B50000))

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

HTH,
Bernie
MS Excel MVP