Calculate Median in a filtered list

[metricsman]

How can I find the median in a filtered list of numbers?
I don't see median as a function of subtotal.

Thanks in advance.

 

[stanleydgromjr]

metricsman,

Try this link:
http://www.mrexcel.com/forum/archive/index.php/t-137865.html


Have a great day,
Stan

 

[barry houdini]

If your list is in A2:A100 try this formula [edited]

=MEDIAN(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),A2:A100))

confirmed with CTRL+SHIFT+ENTER

 

[jpsartrean]

If I'm understanding you correctly, you could employ a workaround I've used numerous times before with subtotals.

Perform your subtotal and then minimize the view so that you just see the subtotaled rows. Highlight these cells, click on edit, go to , special, and click on visible cells only.

It will now highlight the subtotaled data cells only - you could then paste this data into other cells/worksheets in order to perform the calculations you're looking for...

 

[metricsman]

Thanks for the responses. All are great workarounds.
It would be much easier if the median could be calculated on only the displayed items in a filtered list.

 

[Domenic]

Did you see Barry Houdini's post?

 

[metricsman]

I just tried it and it does exactly what I needed.
Thanks for the heads up Domenic.

 

[Domenic]

I just tried it and it does exactly what I needed.
Thanks for the heads up Domenic.

You're very welcome!

 

[roach7]

Barry's technique is genius! But can someone explain to me how/why it works? Thanks!

 

[barry houdini]

This part

=ROW(A2:A100)-ROW(A2)

returns an array of all integers from 0 to 99 so the OFFSET part

=OFFSET(A2,ROW(A2:A100)-ROW(A2),0))

returns an array of single cell ranges A2 through to A100

and then SUBTOTAL with a first argument of 2 (count) will then count the non-filtered numbers in each range (or each cell as the ranges are only single cells)

So for every cell you get 1, if the cell contains a number and isn't filtered out, zero otherwise, and therefore the IF function returns either the number, if unfiltered, or FALSE. Median then calculates, ignoring FALSE entries.

See here too