Median If not working with certain fields

[joshaer]

I have a worksheet arranged as below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[TD]Boolean 1[/TD]
[TD]Boolean 2[/TD]
[TD]Metric[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]UK[/TD]
[TD]Europe[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UK[/TD]
[TD]Europe[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]USA[/TD]
[TD]Americas[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]UK[/TD]
[TD]Europe[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]USA[/TD]
[TD]Americas[/TD]
[TD]False[/TD]
[TD]True[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]USA[/TD]
[TD]Americas[/TD]
[TD]True[/TD]
[TD]True[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]USA[/TD]
[TD]Americas[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]UK[/TD]
[TD]Europe[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

The Country and Region fields are completed via an index(match()) from another sheet which contains a text list of cities, countries, regions, etc.

I am trying to use the following formula: ={median(if(B2:B9="Europe",E1:E9))} to return the European median, but for some reason it doesn't work.

When I Evaluate the formula it recognises the cells filled with "Europe" as TRUE, but it then doesn't end up calculating the median over the range.

I mention the boolean fields as I can successfully run median(if()) on them (i.e. return the median where both are true, for example).

I have checked that it is not a name conflict or that I haven't got any spaces etc. hidden somewhere, but can't think why it won't calculate the Median when I ask it to reference the text fields. Any ideas?

Thanks!

 

[Aladin Akyurek]

It should be 2 for Europe, right?

 

[MarcelBeug]

Your if-statement is lacking the FALSE part and should be ={median(if(B2:B9="Europe",E1:E9,""))}

 

[joshaer]

Thanks, but this doesn't solve the problem - I tested.

In general, it's not necessary for this formula as I don't want anything in FALSE, right?

 

[joshaer]

Hi - yes, that's correct.

The actual data set is 17,000 rows..!

 

[AlKey]

You should change E1 to E2 in your formula and use CTRL+SHIFT+ENTER key combination when entering array formulas

=MEDIAN(IF(B2:B9="Europe",E2:E9)

 

[SpillerBD]

Or shouldn't the ranges match in the number of rows.
=MEDIAN(IF(B1:B9="Europe",E1:E9))
or:
=MEDIAN(IF(B2:B9="Europe",E2:E9))

 

[Aladin Akyurek]

Hi - yes, that's correct.

The actual data set is 17,000 rows..!

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Country[/td][td]Region[/td][td]Boolean 1[/td][td]Boolean 2[/td][td]Metric[/td][td][/td][td]Europe[/td][/tr]

[tr][td]

2​

[/td][td]UK[/td][td]Europe[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

5​

[/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]UK[/td][td]Europe[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]USA[/td][td]Americas[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

4​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]UK[/td][td]Europe[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]USA[/td][td]Americas[/td][td]

FALSE​

[/td][td]

TRUE​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]USA[/td][td]Americas[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

5​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]USA[/td][td]Americas[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

4​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]UK[/td][td]Europe[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[/table]

G2, control+shift+enter, not just enter:

=MEDIAN(IF($B$2:$B$9=G$1,$E$2:$E$9))

See the workbook: https://dl.dropboxusercontent.com/u/65698317/joshaer conditional median.xlsx

As you can see, the array-formula works. What result do you get yourself?

 

[AlKey]

Or shouldn't the ranges match in the number of rows.
=MEDIAN(IF(B1:B9="Europe",E1:E9))
or:
=MEDIAN(IF(B2:B9="Europe",E2:E9))

I does require CSE. Otherwise you'll get the wrong answer.

 

[SpillerBD]

Hi - yes, that's correct.

The actual data set is 17,000 rows..!

Named Dynamic Range or Table Data.... probably be better in the long run.