Geomean based on IF condition not working

Malick

New Member
Joined
Jul 10, 2014
Messages
18
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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
 
Upvote 0
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'.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,405
Messages
6,165,864
Members
451,988
Latest member
boo203

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top