Weighted average

[Guest]

I would like to do the following calculation. Say in column A I have values
in A1 to A10. in cell B10 I would like to do the following calculation :

[(A10*1)+(A9*2)+(A8*3)]/[A10+A9+A8] in B9 it would be
[(A9*1)+(A8*2)+(A7*3)]/[A90+A8+A7] etc.

For the above, the the period is 3 (A10 to A8 and A9 to A7), I would also
like to have a variable to change the number of periods. If it is 5 then use
A10 to A6 etc.

 

[Ken Johnson]

Hi Pierre,

Try this in B10 then fillup to B3


=(INDIRECT(ADDRESS(ROW(),1))*1 + INDIRECT(ADDRESS(ROW()-1,1))*2
+INDIRECT(ADDRESS(ROW()-2,1))*3)/SUM(INDIRECT(ADDRESS(ROW()-2,1)&":"&ADDRESS(ROW(),1)))

Ken Johnson

 

[Ken Johnson]

Hi Pierre,
ignore my last reply, I lost the plot a trifle. It gives the correct
result for period of three but it is ridiculously and unnecessarily
complex. Try instead..

=SUM(A8:A10,A8:A9,A8)/SUM(A8:A10) in B10 then fill up to B3

A variable for other periods is a challenge.

Ken Johnson

 

[bplumhoff]

Hi Pierre,

One solution (in cell B10):
=SUMPRODUCT(A8:A10,{3;2;1})/SUM(A8:A10)

Regards,
Bernd

 

[David Biddulph]

I would like to do the following calculation. Say in column A I have values
in A1 to A10. in cell B10 I would like to do the following calculation :

[(A10*1)+(A9*2)+(A8*3)]/[A10+A9+A8] in B9 it would be
[(A9*1)+(A8*2)+(A7*3)]/[A90+A8+A7] etc.

For the above, the the period is 3 (A10 to A8 and A9 to A7), I would also
like to have a variable to change the number of periods. If it is 5 then
use
A10 to A6 etc.

That doesn't look like a weighted average.

To get a weighted average you'd need to divide by the sum of the weights,
not by the sum of the values. What your formula gives is a weighted average
of the weights (weighted by the values), so if the values in column A are
identical, your average comes out as 2.

[And I assume that you mean A9, not A90?]