Multiplying only the numbers that are greater than zero

[Rnkhch]

Hello,

I have a range of cells that contain variuos positive or zero numbers, and I want to calculate the geometric mean of the range, but I need a way to exclude the zeros. I tried to use the GEOMEAN function hoping it will ignore the zeros, but it didn't. I tried to write some kind of combination of IF and PRODUCT functions, but wasn't successful. And unfortunately the PRODUCTIF function doesn't exist yet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]N1[/TD]
[TD]N2[/TD]
[TD]N3[/TD]
[TD]N4[/TD]
[TD]N5[/TD]
[TD]Geometric mean[/TD]
[/TR]
[TR]
[TD]4.6[/TD]
[TD]8.5[/TD]
[TD]9.2[/TD]
[TD]7.3[/TD]
[TD]0[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]3.9[/TD]
[TD]4.4[/TD]
[TD]9.1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]2.3[/TD]
[TD]8.2[/TD]
[TD]1.7[/TD]
[TD]5.2[/TD]
[TD]4.6[/TD]
[TD]3.77529[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.

P.S. If there are zeros in the range of cells, they would appear at the rightmost column(s), if this info is of any help.

 

[Aladin Akyurek]

In F2 control+shift+enter, not just enter, and copy down:

=GEOMEAN(IF(ISNUMBER(A2:E2),IF(A2:E2>0,A2:E2)))

 

[Rnkhch]

Thank you, but I keep getting the "#NUM!" error with your formula. (Could it be happening because the result of the outer IF statement is FALSE causing the GEOMEAN to not go through?)

 

[Aladin Akyurek]

Thank you, but I keep getting the "#NUM!" error with your formula. (Could it be happening because the result of the outer IF statement is FALSE causing the GEOMEAN to not go through?)

Did you apply control+shift+enter? That is, press down the control and the shift keys at the same time, while you hit the enter key? If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[Aladin Akyurek]

See the workbook implementing the suggested formula:
https://www.dropbox.com/s/j08py6w98lr3pzi/Rnkhch%20conditional%20GEOMEAN.xlsx?dl=0

 

[Rnkhch]

Thanks! I figured it. It was a bit tricky I realized I have to press

control+shift+enter while the cursor is still at the formula bar after typing the formula.​

 

[Aladin Akyurek]

Thanks! I figured it. It was a bit tricky I realized I have to press

control+shift+enter while the cursor is still at the formula bar after typing the formula.​

Great. Thanks for the update.