Geometric Mean is a useful mean and is applied only for +ve values. Hence, you will need to ignore <=0 values while calculating Geometric Mean. It is generally used where %ages are involved. For example, population growth for first year is 30%, for second year is 25% and for third year, it is 15%. Then Geometric Mean is used to calculate not Arithmetic Mean.
Generally, Geometric Mean is calculated by the formula =GEOMEAN(A1:A10)
It would give error if the range contains <=0 values. There are various ways to deal with it and most commonly used way is to ignore <=0 values while calculating Geometric Mean.
To ignore <=0 values, you must use an Array formula i.e. which must be entered by pressing CTRL+SHIFT+ENTER.
=GEOMEAN(IF(A1:A10>0,A1:A10))
The above formula takes into account only those values which are positive.
Bonus Tip – When %age growth are involved, you will need to use following ARRAY formula to calculate Geometric Mean –
=GEOMEAN(IF(A1:A10>0,(1+A1:A10)))-1
Don't forget to format your result as %age.
What if I am trying to do a geometric mean calculation with values of "<1" that I want converted to "1" instead of ignoring?
Use below Array formula i.e. confirm with CTRL+SHIFT+Enter
=GEOMEAN(IF(A1:A10<1,1,A1:A10))