Average (geometric)

[Guest]

Hi,

I want to find the average performance for a portfolio, which include shares
with a positive and negative performance. How can I calculate the average
performance, when I have negative numbers? Geomean does not work, and simple
average does not produce a theoretical correct number.

 

[Ron Rosenfeld]

Hi,

I want to find the average performance for a portfolio, which include shares
with a positive and negative performance. How can I calculate the average
performance, when I have negative numbers? Geomean does not work, and simple
average does not produce a theoretical correct number.

Add one to the percentages; compute the GEOMEAN and subtract 1 from the result.

This can be done either in a helper column or, if that is not convenient, you
could use the *array-entered* formula:

=GEOMEAN(rng+1)-1

Replace "rng" with the cell reference to your list performance percentages.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron