Geomean based on IF condition not working

[Malick]

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.

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

 

[Andrew Poulsom]

You have two Tables? How are they related?

 

[Malick]

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".

=IFERROR(AVERAGEIFS(TableaCalculation[Final Score],Table1[City],[@City]),0)

So I was hoping that Geomean would also work with an IF condition.

 

[Andrew Poulsom]

Try:

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

confirmed with Ctrl+Shift+Enter.

 

[Malick]

Hi Andrew,

Unfortunately, it produces the same result as before.

 

[barry houdini]

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]

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]

Example:

Excel 2010
	A	B	C	D
1	City	Col2		Final Score
2	a	114.1227		407
3	b	614.8756		531
4	c	263.7271		126
5	d	885.4987		884
6	e	254.1712		77
7	a	114.1227		32
8	b	614.8756		712
9	c	263.7271		552
10	d	885.4987		887
11	e	254.1712		839
Sheet1

Cell Formulas
Range	Formula
B2	{=GEOMEAN(IF([City]=[@City], TableaCalculation[Final Score]))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Malick]

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.

[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]

Another example:

Excel 2010
	A	B	C	D	E	F
1	Table1		TableaCalculation		Table2
2	City		Final Score		City	Col2
3	a		407		a	114.1227
4	b		531		b	614.8756
5	c		126		c	263.7271
6	d		884		d	885.4987
7	e		77		e	254.1712
8	a		32		a	114.1227
9	b		712		b	614.8756
10	c		552		c	263.7271
11	d		887		d	885.4987
12	e		839		e	254.1712
Sheet1

Cell Formulas
Range	Formula
F3	{=GEOMEAN(IF([City]=Table1[@City], TableaCalculation[Final Score]))}
Press CTRL+SHIFT+ENTER to enter array formulas.