# Create an excel formula to calculate a Weighted Moving Average of a single datacolumn for nnumber of periods

I have a single column of data in column A. In column C I would like to calculate the WMA for n periods.
So, if I chose 50 periods then in C100, the formula would calculate the sumproduct(A50:A100;(B50:B100)/sum(B50:B100)

or if I chose 20 periods,then in C101, the formula would calculate the sumproduct(A80:A101;(B80:B101)/sum(B80:B101)

The problem is how to create the values in column B.

To be clear, the end result would have Column A with the data and column C with the corresponding WMA, so that I can plot a graph of the two lines. I would have an unput cell where I can change the value of "n" and the graph would change automatically.

So I need a formula rather than a macro. Is this possible?

Thansk

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
So I take it that column B doesn't actually have any values, you just want it to be 1 to n for the last n entries?
If that's the case you don't need to calculate column B directly you can just include that in the formula, e.g. C101 would be
=SUMPRODUCT(OFFSET(A101,0,0,-D\$1),ROW(INDIRECT("1:"&D\$1)))/(D\$1*(1+D\$1)/2)
where your "n" value is in D1
of course there needs to be at least n previous values otherwise you get #REF! error
regards, barry
Commented:
.....I may not have made it clear, that formula can be copied up and down column C as required
barry

Experts Exchange Solution brought to you by