From the help files for MEDIAN, we see that "If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included." So one solution would be (let's say in column F) to set an IF formula that returns the value in column E if the criteria are met, and
"" or similar if the criteria are not met. Then just take the median of column F.
Suppose I want to know the median cuteness only of brown-eyed dogs:
Which gives the required result:
This is not a brilliant solution, but it's a workable one. You get to see very explicitly which cells have been selected, so you can see immediately if you've used the right
IF, but the downside is you might prefer not to have a column used in this way.
The "smarter" method is to use an array formula, but this doesn't give you such a visual check on which cells you selected:
Remember to press CONTROL-SHIFT-ENTER to finish entering the array formula; the enter key alone is not enough. The
IF in the array formula effectively selects only those cells in E2:E9 that match the criteria, and the
MEDIAN will then only apply to the cells you want. This means you can then do something like this to get a table of medians according to various criteria:
Take care with the absolute and relative cell references to make it easier to drag the formula! For instance the array formula in
H2 should be as follows; note that in
H$1 the row is fixed but the column will vary as you drag, and the reverse for
This gives a handy table of results.