Median Function - Compatible with Subtotals?

[Jim Garman]

I have a spreadsheet set up with "AutoFilter" so that I
can easily filter for rows that contain specific values.
At the bottom of the spreadsheet is a "Total" row that
uses the "subtotat" function to dynamically update the
totals based on what I sorted by.

My question is how can I also find the median of these
subsets of data? When I use the median formula it insists
on returning the median of all values in the range, not
just the ones showing in the filter.

This is probably a dumb question, but any help would be
appreciated. I found a work around to do sub-averages,
but can't figure out median.

Thanks,

Jim G.

 

[Peo Sjoblom]

One way, assume the values are in A2:A100

=MEDIAN(IF(SUBTOTAL(9,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),,1))>0,A
2:A100))

entered with ctrl + shift & enter