Moving Weighted Average formula

[Ori]

Hello all, I was wondering if anyone knew of an excel formula that would
let me calculate a weighted moving average, for data that is going down
a column in excel.

I have a formula for a simple moving average, however am having trouble
thinking of one for a weighted moving average.

 

[Dave F]

This may help:http://www.swpp.org/newsletter/summer06/advancedtopics.html

 

[Ori]

Thanks Dave, however I was thinking of something more general, as I
often need to change the moving average period, and a formula like that
gets a bit cumbersome when the period is > 20.

 

[Nikki]

by weighted do you mean you have two columns and you want to get a WA?
if so you can use:
=sumproduct(columnA,ColumnB) which is =(a1xb1+a2*b2+...)/(a1+b2+...)

Hope this works, otherwise please provide more detail.

Regards-

 

[Ori]

No, I only have one column of data I want the MWA from.

eg: B1:B4 is :10,20,15,14

3 period moving average in C3 is (10*1 + 20*2 + 15*3)/(3*2*1)
3 period moving average in C4 is (20*1 + 15*2 + 14*3)/(3*2*1)

etc

I cannot use another column and multiply the values, as the values I
multiply by (the weights) change for each cell.

Hope this makes some sense .

Ori

 

[Ori]

Hmm, come to think of it, I may just have to write out that formula a
few times. What I currently have (with non weighted MAs) is a cell at
the top of the column which determines the period, so I can change that
and all the formulas are linked to it.

I just cant figure out how to do that with a weighted average.