geomean

[stephen]

How do I calculate a geometric average which allows for
negative % values?

 

[RK]

I don't think it can be done directly. Create another column of data which
is the 1+ % values, and do a GEOMEAN of this column. Subtract 1 from the
result.

 

[James Silverton]

I don't think it can be done directly. Create another column of data which
is the 1+ % values, and do a GEOMEAN of this column. Subtract 1 from the
result.

AFAIK, the geometric mean would be an imaginary number if the number
of negative values was odd. In any case, unlike an arithmetic mean,
you can't get the geometric mean by adding a constant to the values
and then subtracting it from the calculated mean. Try it if you like
with a set of random numbers.

 

[Dana DeLouis]

Just to add to James's idea. A custom function might be something like
this:

=IMPOWER(PRODUCT(-5,-4,-3,2,7,9),1/6)

returns:

3.83663894520303+2.21508452779637i


The program Mathematica appears to agree.

GeometricMean[{-5., -4, -3, 2, 7, 9}]

3.8366389452030347 + 2.2150845277963738*I

Anyway, take the Real part of Excel's answer ( "=IMREAL(#)" ) if you have an
even number of negative numbers.

HTH.

 

[Harlan Grove]

How do I calculate a geometric average which allows for
negative % values?

At the risk of spawning an argument I had on this subject 15 months ago,
there's no validity to geometric means from samples with nonpositive
numbers. Geometric means assume you're dealing with multiplicative rather
than additive errors, but multiplicative errors would be strictly positive.

So geometric means of samples with negatives aren't meaningful.

 

[James Silverton]

At the risk of spawning an argument I had on this subject 15 months ago,
there's no validity to geometric means from samples with nonpositive
numbers. Geometric means assume you're dealing with multiplicative rather
than additive errors, but multiplicative errors would be strictly positive.

So geometric means of samples with negatives aren't meaningful.

I'm grateful for the previous post from Dana De Louis reminding me of
IMPOWER. I had written a partial function involving complex logs but
it was really only for the sake of interest since I am not the
original poster. I have to admit (and thus I agree with Harlan Grove)
that I can't remember any recent time in which I've ever wanted to
calculate a geometric mean for statistical or other purposes. I
suppose a Google search might turn up some interesting things but I
can't personally recall any uses of geometric means by anyone!

 

[Dana DeLouis]

I believe what RK was trying to show was the following idea. Suppose you
invest $100.
Say you lose 5% the first & second year, but gain 10% in the next 4 years.

{-0.05, -0.05, 0.1, 0.1, 0.1, 0.1};

At the end of 6 years you would have $132.13 dollars

100*(0.95*0.95)*(1.1*1.1*1.1*1.1)
132.135

This would be similar to saying that "on average," I had a 4.75% return.

100*1.0475377^6
132.135

If we add 1 to the values of {-0.05, -0.05, 0.1, 0.1, 0.1, 0.1}, we get the
same GeometricMean

GeometricMean[{0.95, 0.95, 1.1, 1.1, 1.1, 1.1}]
1.0475377

Then, by subtracting 1, we can say on average, we had a 4.75% return.

Hope I said this correctly. :>)