# Geomean based on IF condition not working

#### Malick

##### New Member
Hi,

I am trying to use the Geomean formula with an if condition but it is not working. I need to calculate geomean of values where my if condition is TRUE. Can you please help me fix the code. The current code is fetching GEOMEAN for the entire column of my table, ignoring the IF condition. I only need geomean of 'Final Score' values where my city is matched. Thanks.
Code:
``=IF(Table1[@[city]]=[@City], GEOMEAN(TableaCalculation[Final Score]),0)``

Last edited:

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Andrew Poulsom

##### MrExcel MVP
You have two Tables? How are they related?

#### Malick

##### New Member
Hi Andrew,
Yes I have two tables. The city value resides in one table "Table1" and the final score for that city resides in another table called "TableaCalculation". Will it be a problem if they were in two different tables? earlier I could use the below formula to calculate the arithmetic mean of values from my TableaCalculation".
Code:
``=IFERROR(AVERAGEIFS(TableaCalculation[Final Score],Table1[City],[@City]),0)``
So I was hoping that Geomean would also work with an IF condition.

#### Andrew Poulsom

##### MrExcel MVP
Try:

=GEOMEAN(IF([City]=[@City], TableaCalculation[Final Score]))

confirmed with Ctrl+Shift+Enter.

#### Malick

##### New Member
Hi Andrew,

Unfortunately, it produces the same result as before.

#### barry houdini

##### MrExcel MVP
Did you confirm with CTRL+SHIFT+ENTER as Andrew advised?

If you do that correctly you'll get curly braces like { and } around the formula

#### Malick

##### New Member
Hi Barry,

Yes I did that to enter it as an array formula but again it is producing geomean of the entire column instead of limiting it to the values for a '@city'.

#### Andrew Poulsom

##### MrExcel MVP
Example:

Excel 2010
ABCD
1CityCol2Final Score
2a114.1227407
3b614.8756531
4c263.7271126
5d885.4987884
6e254.171277
7a114.122732
8b614.8756712
9c263.7271552
10d885.4987887
11e254.1712839
Sheet1
Cell Formulas
RangeFormula
B2{=GEOMEAN(IF([City]=[@City], TableaCalculation[Final Score]))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Malick

##### New Member
Thank you sir for the example. But I am still getting the same results.
I'm sorry I was not clear in my original post. I have my values coming from 3 tables for this formula. [City] is one of the column name of my current table where I am inputting this formula. This [City] is being compared with the @City in Table 1 (Table1[@city] and then wherever they are matched, for those rows it should get the 'FinalScore' from TableaCalculation. I think I am missing something here. I could use the AverageIfs function successfully for these 3 tables to get the arithmetic mean , but this one just can't get it work.
Code:
``[COLOR=#333333][FONT=Verdana]{=GEOMEAN([/FONT][/COLOR][COLOR=Blue][FONT=Verdana]IF([COLOR=Red][City]=Table1[@City], TableaCalculation[Final Score][/COLOR])[/FONT][/COLOR][COLOR=#333333][FONT=Verdana])}[/FONT][/COLOR]``

#### Andrew Poulsom

##### MrExcel MVP
Another example:

Excel 2010
ABCDEF
1Table1TableaCalculationTable2
2CityFinal ScoreCityCol2
3a407a114.1227
4b531b614.8756
5c126c263.7271
6d884d885.4987
7e77e254.1712
8a32a114.1227
9b712b614.8756
10c552c263.7271
11d887d885.4987
12e839e254.1712
Sheet1
Cell Formulas
RangeFormula
F3{=GEOMEAN(IF([City]=Table1[@City], TableaCalculation[Final Score]))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
1
Views
172
Replies
8
Views
190
Replies
7
Views
117
Replies
5
Views
118
Replies
1
Views
189

1,191,669
Messages
5,987,949
Members
440,121
Latest member
eravella

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