# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Geometric Mean

## Sir08

Please help to find a single formula to calculate negative and positive stream of returns. The problem is that =geomean formula does not work with negative numbers. 
_Here's an example on how to calculate geometric mean with 5 numbers:_
Stream of numbers: 0.5, -1.4, -6.5, 0.3, -2.7

First step: I have to add 1 to all numbers (they are positive now)
Second step: =Product (multiply all numbers)
Third step (result): find 5th root(there are 5 numbers) of their product (or raise it to 1/5 power)

I can substitute steps 2 & 3 with a formula =geomean() and the answer will be correct except it won't be negative...but i think there should be another solution, a single formula. :Smilie:  
Any idea? Thank you so much!

----------


## martindwilson

1 i believe that  geometric mean only works with positive numbers
2. how does adding 1 to -6.5 make it positive? it then  = -5.5

----------


## sweep

Hi,

You can calculate compound average return using the following array formula.

Remember to use CTRL, SHIFT and ENTER to confirm it

{=GEOMEAN(1+(A1:E1)/100)}

NB  This does assume that the numbers in the range A1:E1 are percentages.

----------


## Sir08

Thank you Dave, the formula makes sense. For some reason i can't enter it even though i used your instructions....

----------


## sweep

No Worries,

Type this into the cell:

=GEOMEAN(1+(A1:E1)/100)

Don't press enter yet.  Hold down the CTRL and SHIFT keys, then tap ENTER.  If done correctly, excel will display it like this:

{=GEOMEAN(1+(A1:E1)/100)}

----------


## Sir08

Great, it works. One last thing: if my retures are negative (geomean formula does not accept 0s and negative #s), is there a way to go around it? thanks

----------


## shg

I'm not sure what the interpretation is of a geometric mean with negative numbers, but

=IF( OR(A1:E1 = -1), 0, GEOMEAN(ABS(A1:E1 + 1) ) )

Still an array formula.

----------


## martindwilson

i take it these are percentages.
you need to convert to decimal multiplier first (because of negative numbers)

so 0.5, -1.4, -6.5, 0.3, -2.7
become 1.005  0.986   0.935   1.003  0.973

see here 
http://www.buzzardsbay.org/geomean.htm

----------


## shg

That makes sense, Martin, thank you.

Then if the numbers are stored as percentages (e.g., 0.1 means 10%), {=GEOMEAN(1+A1:A5)}

If 0.1 means 0.1%, then {=GEOMEAN(1+A1:A5/100)}

If these are rates of return, then the individual inner sums can never be less than 0 (you can't lose more than all your money).

Edit: To return a result in the format as the input data,

{=GEOMEAN(1+A1:A5) - 1} if 0.1 means 10%

{=100 * (GEOMEAN(1+A1:A5/100) - 1)} if 0.1 means 0.1%

----------

