Conditional median in MS Excel

Question

I'm trying to calculate the conditional median of a chart that looks like this:

A  |  B
-------
x  |  1
x  |  1
x  |  3
x  |  
y  |  4
z  |  5

I'm using MS Excel 2007. I am aware of the AVERAGEIF() statement, but there is no equivalent for Median. The main trick is that there are rows with no data - such as the 4th "a" above. In this case, I don't want this row considered at all in the calculations.

Googling has suggested the following, but Excel won't accept the formula format (maybe because it's 2007?)

=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)

Excel gives an error saying there is something wrong with my formula(something to do with the * in the condition) I had also tried the following, but it counts blank cells as 0's in the calculations:

=MEDIAN(IF(A:A = "x", B:B, "")

I am aware that those formulas return Excel "arrays", which means one must enter "Ctrl-shift-enter" to get it to work correctly.

How can I do a conditional evaluation and not consider blank cells?

Answer 1

Nested if statements.

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

Not much to explain - it checks if A is x. If it is, it checks if B is non-blank. Anything that matches both conditions gets calculated as part of the median.

Given the following data set:

A | B
------
x | 
x |     
x | 2
x | 3
x | 4
x | 5

The above formula returns 3.5, which is what I believe you wanted.

Answer 2

Use the Googled formula, but instead of hitting Enter after you type it into the formula bar, hit Ctrl+Shift+Enter simultaneously (instead of Enter). This places brackets around the formula and will treat it as an array.

Be warned, if you edit it, you cannot hit Enter again or the formula will not be valid. If editing, you must do the same thing when done (Ctrl+Shift+Enter).