Well, when averaging rates, it's appropriate to use the harmonic mean. My
guess is one would throw out rates of 0.00% when calculating such, for the
reasons you cite. It should also be noted that 0 values tend to affect
arithmetic averages as well.

You're correct that a weighted average is different than a straight
arithmetic mean.

Perhaps the best answer to the OP's question is to do what he originally
did, however, he should be using absolute values but rather the original
values.
--
Brevity is the soul of wit.


"Jerry W. Lewis" wrote:

=SUM(weights)/SUMPRODUCT(weights,1/data)

This will return #DIV/0 if even one observation in data is zero. If a
single observation is very close to zero, that observation will tend to
dominate all of the other data in the harmonic mean. Are you sure that is
appropriate?

Jerry

"JK" wrote:

Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK