MEDIAN(IF( >80% works, but MEDIAN(IF( <80% does not work?? Anybody who can help?

[olivierv]

Hey guys,

I'm having problems with a MEDIAN(IF( array formula. For example for the table below, I want to calculate the median EV/EBITDA for "all years" and for "all companies" if the sales are more than 80% in the US.

{=MEDIAN(IF(geography!$P:$P>80%,geography!$X:$X))}
{=MEDIAN(IF(geography!$P:$P<80%,geography!$X:$X))}

The thing is that my formula works for "more than", i.e. >80%. But the other way round, when trying to calculate the median EV/EBITDA multiple for all companies and all years <80% sales in the US I get 0... As you can see from the data below, this is impossible as there are companies that actually have less than 80% sales in the US (and there's lots of more data!!).

Does anyone know what the mistake is?

 

[Jonmo1]

Probably due to blanks and the use of entire column references.
Blanks will be counted as 0, so 0<80% = True, therefor MANY 0's will be included in the median calculation.

Either restrict your ranges to the actual used range, so as to avoid looking at blanks. <-this would be the preferred method.
Or
Add a criteria for P:P <> ""
=MEDIAN(IF(geography!$P:$P<>"",IF(geography!$P:$P<80%,geography!$X:$X)))

 

[olivierv]

Probably due to blanks and the use of entire column references.
Blanks will be counted as 0, so 0<80% = True, therefor MANY 0's will be included in the median calculation.

Either restrict your ranges to the actual used range, so as to avoid looking at blanks. <-this would be the preferred method.
Or
Add a criteria for P:P <> ""
=MEDIAN(IF(geography!$P:$P<>"",IF(geography!$P:$P<80%,geography!$X:$X)))

Thanks Jonmo1, your solution works!

 

[olivierv]

By the way, quick question: I get significant different numbers if I use ctrl + shift + enter instead of just hitting the enter button... how's that possible? I know the first one is an array formula, but I learned that usually it doen't really yield a different number?

 

[Jonmo1]

ADVERTISEMENT

You're welcome.

 

[olivierv]

You're welcome.

Could you please help me out with my question above? Sorry, I think we just crossed each other

 

[Jonmo1]

ADVERTISEMENT

but I learned that usually it doen't really yield a different number?

What is the source of where you learned that? It's wrong.
Although I suppose it depends on your interpraitation of 'Usually', that's a pretty vague/ambiguous term.
I would say the opposite, it usually DOES yield a different result.

Anyway,
Pressing CTRL + SHIFT + ENTER tells excel to process the formula as an array, to evaluate all cells in the range.
Without the CTRL + SHIFT + ENTER
That formula will really only look at 1 cell.
The one in the same row as the cell you put the formula in.

so say you put this in cell C5 for example
=MEDIAN(IF(geography!$P:$P>80%,geography!$X:$X))
It will really only look at P5 and X5
So it's the same as
=MEDIAN(IF(geography!$P$5>80%,geography!$X$5))

 

[olivierv]

Oops, haha I think I just learned what an array formula really does... Thanks for the very helpful advice, really appreciate it! Thanks

 

[Jonmo1]

You're welcome