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

olivierv

New Member
Joined
Jul 25, 2014
Messages
29
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?





 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Caribeiro77

Well-known Member
Joined
Sep 24, 2010
Messages
1,261
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?
 

olivierv

New Member
Joined
Jul 25, 2014
Messages
29
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,800
Messages
5,446,554
Members
405,406
Latest member
tuxy

This Week's Hot Topics

Top