Geo average

[Guest]

How can I calculate the avg. of %-changes, when they include negative
numbers? Geomean does not work with negative numbers. Simple "average" func.
does not produce a theoretical correct number when negative numbers are
involved.

 

[Ron Rosenfeld]

How can I calculate the avg. of %-changes, when they include negative
numbers? Geomean does not work with negative numbers. Simple "average" func.
does not produce a theoretical correct number when negative numbers are
involved.

See my answer to your previous post
--ron

 

[Harlan Grove]

Stephen wrote...

How can I calculate the avg. of %-changes, when they include negative
numbers? Geomean does not work with negative numbers. Simple "average" func.
does not produce a theoretical correct number when negative numbers are
involved.

You should NEVER average percentage changes. You should convert
percentage changes to multiplicative factors and average them. If ANY
percentage changes are <= -100%, your geometric mean of percentage
changes is meaningless.

So, given +5%, +10%, -3%, +6%, -5%, convert these to 1.05, 1.10, 0.97,
1.06 and 0.95, and calculate the geometric mean (1.0205, rounded), then
subtract 1 from it to give the geometric average percentage change,
+2.05%.

The best numerical approach to averaging a range of percentage changes
(RPC) would be the array formula

=EXP(AVERAGE(LN(RPC)))

This avoids problems arising from the shortsighted implementation of
GEOMEAN in Excel, basically, PRODUCT(RPC)^(1/COUNT(RPC)), which suffers
from overflow and underflow.

 

[Harlan Grove]

Stephen wrote...

How can I calculate the avg. of %-changes, when they include negative
numbers? Geomean does not work with negative numbers. Simple "average" func.
does not produce a theoretical correct number when negative numbers are
involved.

You should NEVER average percentage changes. You should convert
percentage changes to multiplicative factors and average them. If ANY
percentage changes are <= -100%, your geometric mean of percentage
changes is meaningless.

So, given +5%, +10%, -3%, +6%, -5%, convert these to 1.05, 1.10, 0.97,
1.06 and 0.95, and calculate the geometric mean (1.0205, rounded), then
subtract 1 from it to give the geometric average percentage change,
+2.05%.

The best numerical approach to averaging a range of percentage changes
(RPC) would be the array formula

=EXP(AVERAGE(LN(1+RPC)))

This avoids problems arising from the shortsighted implementation of
GEOMEAN in Excel, basically, PRODUCT(RPC)^(1/COUNT(RPC)), which suffers
from overflow and underflow.

 

[Harlan Grove]

Harlan Grove wrote...
....

=EXP(AVERAGE(LN(RPC)))

....

Disregard. Forgot to add 1.

 

[Ron Rosenfeld]

This avoids problems arising from the shortsighted implementation of
GEOMEAN in Excel, basically, PRODUCT(RPC)^(1/COUNT(RPC)), which suffers
from overflow and underflow.

I was not aware of that issue. Thank you for pointing it out.

Does it become a problem in averaging stock return percentages?


--ron

 

[Guest]

Thanks alot - is there any way I can modify the expression to allow for
blanks or zeros in the array I am chosing the percentrage chg. from?

 

[Harlan Grove]

Stephen wrote...

Thanks alot - is there any way I can modify the expression to allow for
blanks or zeros in the array I am chosing the percentrage chg. from?

....

Excluding blanks or text is OK, but zeros in the context of percentage
changes are legitimate values meaning NO CHANGE or period T value
equals period T+1 value. For this reason I won't help you exclude zeros
because you'd wind up with misstated (therefore, misleading) averages.
As for including only the numbers in RPC, use the array formula
=EXP(AVERAGE(IF(ISNUMBER(RPC),LN(1+RPC))))-1

 

[Harlan Grove]

Ron Rosenfeld wrote...

I was not aware of that issue. Thank you for pointing it out.

Does it become a problem in averaging stock return percentages?

This is a problem only when averaging a large number of large or small
positive numbers. So probably not a problem for averaging stock return
precentages for a few stocks over a few years. However, I won't use
GEOMEAN for anything, and I post what I use.

 

[Ron Rosenfeld]

This is a problem only when averaging a large number of large or small
positive numbers. So probably not a problem for averaging stock return
precentages for a few stocks over a few years. However, I won't use
GEOMEAN for anything, and I post what I use.

Good advice

--ron

 

[Myrna Larson]

However, I won't use GEOMEAN for anything

Could you explain your reasons?

 

[Harlan Grove]

Myrna Larson wrote...

Could you explain your reasons?

I use some datasets with large numbers of large or small numbers
(sometimes I look at X, sometimes at 1/X). These are the sorts of
datasets which generate overflow or underflow errors when one applies
the simplistic algorithm for geometric means which Excel's GEOMEAN
uses. I'm lazy, so I generally stick to one way of doing most tasks so
they become second nature. In order to avoid this particular problem, I
always use EXP(AVERAGE(LN(.))), so I don't get nasty surprises when I
get lazy.

By the same token, I also use DEVSQ(.)/COUNT(.) rather than VARP all
the time since Jerry W. Lewis clued me into that.

I don't know about you, but if I know some functions have problems in
extreme cases, I avoid them in all cases. Makes my life easier.

 

[Guest]

Hi again,

The formula you advised did wonders. Thanks so much. Now I want to allow for
assigning different weights to the percentage changes....can this be done in
one formula? I have a collumn with the weights beside the percentage changes.

Thanks,
Stephen

 

[Harlan Grove]

The formula you advised did wonders. Thanks so much. Now I want to
allow for assigning different weights to the percentage changes....
can this be done in one formula? I have a collumn with the weights
beside the percentage changes.

....

If the range of weights were named W and were the same size and shape as
RPC, try the array formula

=EXP(SUM(IF(ISNUMBER(RPC),(1+RPC)*W))/SUM(IF(ISNUMBER(RPC),W))-1

 

[Harlan Grove]

Harlan Grove wrote...
....

=EXP(SUM(IF(ISNUMBER(RPC),(1+RPC)*W))/SUM(IF(ISNUMBER(RPC),W))-1

I screwed up and forgot the LN call. Make that
=EXP(SUM(IF(ISNUMBER(RPC),LN(1+RPC)*W))/SUM(IF(ISNUMBER(RPC),W))-1

 

[Harlan Grove]

Harlan Grove wrote...
....

=EXP(SUM(IF(ISNUMBER(RPC),(1+RPC)*W))/SUM(IF(ISNUMBER(RPC),W))-1

....

This'll teach me not to test. Also missing a right parenthesis. It
should be

=EXP(SUM(IF(ISNUMBER(RPC),LN(1+RPC)*W))/SUM(IF(ISNUMBER(RPC),W)))-1
This time I made sure it works.

 

[Myrna Larson]

Thanks for the insights. It's a function I haven't used enough to hit the
bugs.

 

[Harlan Grove]

Myrna Larson wrote...

Thanks for the insights. It's a function I haven't used enough to hit the
bugs.

If you want to model individual's annual incomes with a lognormal
distribution and you have a sample of 100 individuals' incomes in whole
US$s, you'll hit this bug (100 * ~5E4 -> ~5E400 >> Excel's FP max).