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

#### olivierv

##### New Member
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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Jonmo1

##### MrExcel MVP
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)))

Last edited:

#### olivierv

##### New Member
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

##### New Member
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?

You're welcome.

#### olivierv

##### New Member
You're welcome.

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

#### Jonmo1

##### MrExcel MVP
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

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

You're welcome

Replies
0
Views
716
Replies
3
Views
1K
Replies
2
Views
1K
Replies
1
Views
2K
Replies
2
Views
320

1,195,663
Messages
6,011,013
Members
441,579
Latest member
satishrazdhan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back