ExcelBanter - View Single Post - Weighted Average of positive and negative %

[email protected]

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.