Forum Discussion

Diego1265's avatar
Diego1265
Copper Contributor
Feb 23, 2022

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

excel

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 23, 2022

    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%.

     
    • Diego1265's avatar
      Diego1265
      Copper Contributor
      Feb 23, 2022
      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.

Resources