Auto Filter - Calculating Median

[John Y]

Say i want to auto filter a file that has 10 columns of data. I want to
filter by three -five different columns and i want to calculate the median,
max, and min of the remaining information only. How would i go about that?

 

[Roger Govier]

Hi John

For the Max and Min there is a built in function - Subtotal.

Say your data you want to find the max and Min for is in column H
=SUBTOTAL(5,H:H) will give the Min of the visible cells in column H
=SUBTOTAL(4,H:H) will give the Max of the visible cells in column H
(In both cases, I am assuming the formula is not entered in column H itself.
If it is an inserted row above your header in column H, then you would have
to make the range H3:Hxxxxx)

Average uses 1 as the parameter in Subtotal, but there is no parameter for
Median.