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?
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.