=MEDIAN(IF(AND function returns #NUM! What am I doing wrong?

[olivierv]

Hi guys,

I need to calculate the median EV/EBITDA for e.g. year 2009 when sales in the US are > 50%.
=MEDIAN(IF(A1:A21=2009,IF(B1:B21>50%,G1:G21,0)))

This formula works.

But now want to built a new function with a nested IF function, but I can't get it right. For example: return the median EV/EBITDA for 2009 with North America >50%, EU>10% and EM>10%.

I used MEDIAN(IF(A1:A21=2009,IF(AND(B1:B21>50%, C1:C21>10%, D1:D22>50%), G1:G21,0))) but it keeps returning #NUM.

Anyone that has a solution or can you tell what I'm doing wrong?

 

[Caribeiro77]

I haven't tried your formula, but is this range right?

MEDIAN(IF(A1:A21=2009,IF(AND(B1:B21>50%, C1:C21>10%, D1:D22>50%), G1:G21,0)))

Shouldn't it be D21?

 

[Marcelo Branco]

Use multiple nested IFs instead of AND (as you did in the first formula)

=MEDIAN(IF(A1:A21=2009,IF(B1:B21>50%,IF(C1:C21>10%,IF(D1:D21>50%, G1:G21,0)))))
Ctrl+Shift+Enter

or multiply the conditions

=MEDIAN(IF((A1:A21=2009)*(B1:B21>50%)*(C1:C21>10%)*(D1:D21>50%), G1:G21,0))
Ctrl+Shift+Enter

See
Daily Dose of Excel » Blog Archive » Logical operations in array formulas

Hope this helps

M.

 

[olivierv]

Thanks, this works Marcello Brando. The only thing is that when I change the ">" into "<" it returns #NUM!. But I assume this is because of there are rarely any companies into my datasheet that fulfill the <50%, <10% and <50% criteria.