geomean

  • Thread starter Thread starter stephen
  • Start date Start date
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.
 
RK said:
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.
Click to expand...
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.
 
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.
 
stephen said:
How do I calculate a geometric average which allows for
negative % values?
Click to expand...

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.
 
Harlan Grove said:
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.
Click to expand...

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!
 
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. :>)
 
Back
Top