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

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

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

=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

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

JK,
Having followed the discussion so far:

First, I think you need a geometric mean and not a harmonic mean. If
you have growth rates in B1:B6 then the geometric mean would be the
following *array* formula:
=PRODUCT(1+B1:B6)^(1/ROWS(B1:B6))-1

More simply, it can also be produced with GEOMEAN (again *array*):
=GEOMEAN(1+B1:B6)-1

Then, if this is x, the end value should be equal to
=StartValue*(1+x)^ROWS(B1:B6)

A variant of this formula could possibly be used for weighted geometric
mean but I am at a loss as to what your "weights" imply. Also I cannot
understand what you mean when you say:
I have the following numbers and corresponding growth rates

If you are tracking a single time period, have different amounts (e.g.
like in a portfolio) and ask what is the yield of the portfolio then
negative numbers should not be a problem. Also in this case there is
no issue for Geometric mean. The yield would be:

=SUMPRODUCT(A1:A6,1+B1:B6)/SUM(A1:A6)-1

Does either suit you?

HTH
Kostis Vezerides

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

GEOMEAN can easily overflow with large n or large values or underflow with
small values. A better way to calculate GEOMEAN(data) would be the array
formula
=EXP(AVERAGE(LN(data)))
which leads immediatly to the weighted geometric mean
=EXP(SUMPRODUCT(weights,LN(data))/SUM(weights))

It is not at all clear to me that the linear shift you propose (to deal with
negative values) is appropriate for dealing with this data.

Jerry

"vezerid" wrote:

JK,
Having followed the discussion so far:

First, I think you need a geometric mean and not a harmonic mean. If
you have growth rates in B1:B6 then the geometric mean would be the
following *array* formula:
=PRODUCT(1+B1:B6)^(1/ROWS(B1:B6))-1

More simply, it can also be produced with GEOMEAN (again *array*):
=GEOMEAN(1+B1:B6)-1

Then, if this is x, the end value should be equal to
=StartValue*(1+x)^ROWS(B1:B6)

A variant of this formula could possibly be used for weighted geometric
mean but I am at a loss as to what your "weights" imply. Also I cannot
understand what you mean when you say:
I have the following numbers and corresponding growth rates

If you are tracking a single time period, have different amounts (e.g.
like in a portfolio) and ask what is the yield of the portfolio then
negative numbers should not be a problem. Also in this case there is
no issue for Geometric mean. The yield would be:

=SUMPRODUCT(A1:A6,1+B1:B6)/SUM(A1:A6)-1

Does either suit you?

HTH
Kostis Vezerides

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

Jerry W. Lewis wrote:
It is not at all clear to me that the linear shift you propose (to deal with
negative values) is appropriate for dealing with this data.

It is appropriate, but it is not necessary. I had started with "1+g"
for my earlier response out of habit, because that __is__ necessary
when computing the log. But then I realized that SUM(w[i]*g[i]) =
SUM(w[i]*(1+g[i])) - 1.

But all this is academic with respect to the OP's problem because I
believe the OP is not dealing with a time series, but with a collection
of categories. As I explained in my earlier response, we can
demonstrate that in that case, SUM(w[i]*g[i]) is the correct solution
for the OP's problem, and it does matter that some g[i] are negative.

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%

Why do you think that is a problem?

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%

I believe that is the right answer, to the point that you rounded it.

Consider that items #1-6 started with values of 1000 each and grew
according to the rates above. So C1:C6 might contain 1000 each, and
D1:D6 contains ending values, which is computed with formulas like (for
D1) =C1*(1+B1). Then the average growth rate can be computed as
follows:

=sumproduct(A1:A6, D1:D6) / sumproduct(A1:A6, C1:C6) - 1

The result is 6.2%, rounded the same way you did.

Hello JK,

To calculate the weighted average growth rate, you can use the formula:

This formula multiplies each number in column A by its corresponding growth rate in column B, adds up the results, and then divides by the total sum of column A.

However, since you have a negative growth rate in cell B5, you may want to consider using the ABS() function to get the absolute value of the growth rate. This is because the negative growth rate will decrease the overall weighted average growth rate.

To calculate the weighted average growth rate with the absolute value of the growth rates, you can use the formula:

This formula multiplies each number in column A by the absolute value of its corresponding growth rate in column B, adds up the results, and then divides by the total sum of column A.

So, to answer your question, if you want to include the negative growth rate in your calculation, you do not need to use the ABS() function. However, if you want to calculate the weighted average growth rate without the negative growth rate affecting the result, you should use the ABS() function.

1. Use the formula
   Code:

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

   to calculate the weighted average growth rate.
2. Consider using the ABS() function if you have negative growth rates.
3. Use the formula
   Code:

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

   to calculate the weighted average growth rate with the absolute value of the growth rates.