# Geomean alternative for Averageifs

#### Lawrences

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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Marcelo Branco

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

##### MrExcel MVP
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.

Replies
6
Views
212
Replies
3
Views
921
Replies
0
Views
224
Replies
1
Views
209
Replies
1
Views
3K

1,191,190
Messages
5,985,201
Members
439,947
Latest member
fabiannic

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

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