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