Using the Median function with Auto-filter

[BASFMark]

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]

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