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.

Number	Amount	Bin	Geomean
1.25	6	1	=GEOMEAN(IF(AND($A$2:$A$10<=C3,$A$2:$A$10>C2),$B$2:$B$10))
2	545	2
2.5	45	3
2.75	44	4
2.85	453
2.95	23
3.1	56
3.5	57
3.7	524

<tbody>
</tbody>

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.