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!

 

[joshaer]

@Aladin - I'm using CSE. The formula works if I use any other fields as the criteria except for these specific text fields. Can you think of what would prevent them from being used?

@AlKey - correct - that's just a typo in my example; it's correct in my actual. I'm actually working with personal information of a number of people, hence not using actual data in example.

 

[MarcelBeug]

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?

Yes, you're right, apologies, my mistake.

 

[Aladin Akyurek]

@Aladin - I'm using CSE. The formula works if I use any other fields as the criteria except for these specific text fields. Can you think of what would prevent them from being used?
...

Which field(s)? Can you be a bit more specific about the conditions you want when calculating a conditional mean?

 

[joshaer]

Sure -

broadly my data is structured thusly (with some dummy data):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Text field 1[/TD]
[TD]Text field 2[/TD]
[TD]Boolean field 1[/TD]
[TD]Boolean field 2[/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD]Example text 1.1
[/TD]
[TD]Example text 2.1
[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Example text 1.2[/TD]
[TD]Example text 2.2[/TD]
[TD]True[/TD]
[TD]False[/TD]
[TD]Number[/TD]
[/TR]
</tbody>[/TABLE]

The data in the text fields is the output of a lookup formula from another tab. The data in the boolean fields is the result of some if statements referring to fields elsewhere in the sheet. I am trying to calculate the median of certain data in the "Values" field.

I am successfully able to calculate the median based on a criteria from the Boolean fields. However, I cannot calculate median based on values in the Text fields.

However, this is not because the text looks the same, but is not actually equal. For example, if I check that the first value in Text Field 1 = "Example text 1.1" I get TRUE.

If it helps, I can show the steps of the formula evaluator?

 

[Aladin Akyurek]

What is the text fıeld you are testing for? We already tried Europe...

 

[joshaer]

Sorry - it is the test field Europe or Americas. To be clear, the answer 2 is correct and the syntax is correct, but the formula returns #N/A rather than the answer.

 

[Aladin Akyurek]

Sorry - it is the test field Europe or Americas. To be clear, the answer 2 is correct and the syntax is correct, but the formula returns #N/A rather than the answer.

What is the exact formula tha is giving you #N/A?

 

[joshaer]

{=MEDIAN(IF($BO$7:$BO$16907="Europe",$BL$7:$BL$16907))}

In the same sheet, the following formula does work: {=MEDIAN(IF($BQ$7:$BQ$16907=TRUE,$BL$7:$BL$16907))} where BQ is =if(BO="Europe",TRUE,FALSE)

If it affects things, the field of values (i.e. BL7:BL16907) is approx 99% full of "" as it's populated using a countif() with iferror()

 

[Aladin Akyurek]

{=MEDIAN(IF($BO$7:$BO$16907="Europe",$BL$7:$BL$16907))}

In the same sheet, the following formula does work: {=MEDIAN(IF($BQ$7:$BQ$16907=TRUE,$BL$7:$BL$16907))} where BQ is =if(BO="Europe",TRUE,FALSE)

If it affects things, the field of values (i.e. BL7:BL16907) is approx 99% full of "" as it's populated using a countif() with iferror()

When you run:

=COUNTIFS($BO$7:$BO$16907,#N/A)

what result do you get?

 

[SpillerBD]

? I can get it to an error of "#NUM!" several ways. I generated error of "#N/A" by having different number of rows in the arrays.
Did you copy and paste the formula from Excel or did you type that into the thread?