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.
<tbody>
</tbody>
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.
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.
N1 | N2 | N3 | N4 | N5 | Geometric mean |
4.6 | 8.5 | 9.2 | 7.3 | 0 | #NUM! |
3.9 | 4.4 | 9.1 | 0 | 0 | #NUM! |
2.3 | 8.2 | 1.7 | 5.2 | 4.6 | 3.77529 |
<tbody>
</tbody>
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.