Different length moving averages

[Guest]

Hello;

I have several thousand data points listed consecutively in column A of my
spreadsheet. In column B, I use the AVERAGE argument to create a specific
length moving average of all points. I am running a "what-if" study to find
the optimum length moving average. Is there a way to write a macro, I so I
can just supply the length of desired moving average in the top cell of
column B, and I don't have to rewrite the AVERAGE argument each time, and cut
and paste it in every cell?

Thanks.

 

[Myrna Larson]

In B2:

=AVERAGE(OFFSET(A2,-$B$1+1,0,$B$1,1))

and copy it down.

The formula will return an error until you have sufficient cells for the first
average, i.e. if you specify 10 in cell B1, the formulas in B2:B9 will return
errors.