Weighted Average of positive and negative %

[Guest]

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]

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

 

[joeu2004]

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
D16 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, D16) / sumproduct(A1:A6, C1:C6) - 1

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

 

[vezerid]

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

 

[joeu2004]

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*g) =
SUM(w*(1+g)) - 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*g) is the correct solution
for the OP's problem, and it does matter that some g are negative.