Forum Discussion
Geometric mean
Hello, I was wondering how to get a geometric mean when dealing with negative numbers in excel. All I get is "ogiltigt" in Swedish which translates to invalid. Thanks Diego
Diego1265 The help for GEOMEAN says:
"If any data point ≤ 0, GEOMEAN returns the #NUM! error value."
You might use
=PRODUCT(range)^(1/COUNT(range))
but the result would probably be meaningless.
If, for example, you have returns of 10%,-5%,7% and -2% in B2:B5, you may not really want to calculate the geometric mean of those values, but of 100%+10%, 100%-5%, 100%+7% and 100%-2%, and subtract 100% from the result: =GEOMEAN(100%+B2:B5)-100%.
- I still got invalid result, however I divided my numbers with 100 and then later added +1, and from the last number I was able to attain geometric mean. Im not sure however that it is the correct answer.
