geometric mean of a series of negative asset returns

[Dave F]

The GEOMEAN function calculates the geometric mean for a series of
positive numbers.

I have a series of negative numbers (asset returns) for which I want
to calculate the geometric mean.

Is there any easy way to calculate this? The series is about 30
numbers long and I would prefer not to build this manually...

Thanks in advance.

 

[Dave F]

The GEOMEAN function calculates the geometric mean for a series of
positive numbers.

I have a series of negative numbers (asset returns) for which I want
to calculate the geometric mean.

Is there any easy way to calculate this?  The series is about 30
numbers long and I would prefer not to build this manually...

Thanks in advance.

Actually, the following as an array formula seems to work: =GEOMEAN
(F2:F29*-1)*-1

Strange workaround but it makes sense logically.

 

[Ron Rosenfeld]

The GEOMEAN function calculates the geometric mean for a series of
positive numbers.

I have a series of negative numbers (asset returns) for which I want
to calculate the geometric mean.

Is there any easy way to calculate this? The series is about 30
numbers long and I would prefer not to build this manually...

Thanks in advance.

=geomean(rng+1)-1

entered as an array formula
--ron

 

[Ron Rosenfeld]

=geomean(rng+1)-1

entered as an array formula
--ron

Of course, this presumes that negative returns do not reach -100%

--ron

 

[Shane Devenshire]

Hi,

You could also use either of these:

=-GEOMEAN(-F2:F10)
=-GEOMEAN(ABS(F2:F10))

The second is more intuitive, but longer and both are arrays.

Cheers,
Shane

 

[Dave F]

Of course, this presumes that negative returns do not reach -100%

--ron- Hide quoted text -

- Show quoted text -

Negative returns in this series are less than -100%. I think your
solution works best, thanks.

 

[Ron Rosenfeld]

Negative returns in this series are less than -100%. I think your
solution works best, thanks.

Thanks for the feedback. I guess you're fortunate in not losing everything in
this market!
--ron

 

[Dave F]

Thanks for the feedback.  I guess you're fortunate in not losing everything in
this market!
--ron

Well, these aren't actual investments. Just a theoretical portfolio
of value stocks.

 

[Ron Rosenfeld]

Well, these aren't actual investments. Just a theoretical portfolio
of value stocks.

<sigh>. If only my losses had been theoretical.
--ron