Hi there, this formula made into an array is not excluding empty cells. Any ideas?
{=MEDIAN(IF(A$2:A$50000=A2,IF(B$2:B$50000=B2,C$2:C $50000)))}

column A and column B are categories, column C is the number. I want the median of all rows where a and b are equal, and c is not empty.

in this simple example with only one a+b combo, zy median should return the number 2. Using the array, it is returning 1.5

a b c
-----
z y 1
z y 2
z y
z y 3

"tmason" wrote:
this formula made into an array is not excluding empty cells.
Any ideas?
{=MEDIAN(IF(A$2:A$50000=A2,IF(B$2:B$50000=B2,C$2:C $50000)))}

Array-enter the following formula (press ctrl+shift+Enter, not just Enter):

=MEDIAN(IF(A$2:A$50000=A2,IF(B$2:B$50000=B2,
IF(C$2:C$50000<"",C$2:C$50000))))

figured it out

=MEDIAN(IF((A$2:A$50000=A2)*(B$2:B$50000=B2)*(C$2: C$50000<""),C$2:C$50000))

but wait: I don't want the median returned for the cases where col C is empty. Now I'm having trouble with that ...

My Lord ... figured that out, too.

I guess I like talking to myself