Geomean alternative for Averageifs

[Lawrences]

Hi, I am trying to find the geometric mean of a range of values based on another range of data and if that falls into a certain bin width. So for example, finding the geometric mean of the values in column B, only if the values of column A fall within a certain bin width that I set at 1. [TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Amount[/TD]
[TD]Bin[/TD]
[TD]Geomean[/TD]
[/TR]
[TR]
[TD]1.25[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]=GEOMEAN(IF(AND($A$2:$A$10<=C3,$A$2:$A$10>C2),$B$2:$B$10))[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]545[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]45[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.75[/TD]
[TD]44[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.85[/TD]
[TD]453[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.95[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.7[/TD]
[TD]524[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That formula I enter (and then drag down) does not work. It returns #NUM!
Those numbers are fictional, as the actual data has about 600 rows. However the concept is the same

Please can someone help me come up with a formula to calculate the geometric mean of column B, if column A falls within the bin size.

Thanks

 

[Marcelo Branco]

You cannot use AND in this way in array formulas.

Try nested IFs
=GEOMEAN(IF($A$2:$A$10<=C3,IF($A$2:$A$10>C2,$B$2:$B$10)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Marcelo Branco]

As an alternative you can also multiply the conditions. This also works properly
=GEOMEAN(IF(($A$2:$A$10<=C3)*($A$2:$A$10>C2),$B$2:$B$10))
Ctrl+Shift+Enter

M.