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?

Thanks.

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.