Geometric Mean

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!

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.

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)}

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%