Excel expertise... calculating moving averages.

[RedGreenBen]

Having lured you in with that simple title, here's my problem....

I am trying to calculate a MA in Excel (or OO Calc) but (and it's a big but) the MA period is a variable equal to the value of a cell.

I could export all my data from the spreadsheet and do this with some code but if anyone knows of a way to do this within the XLS then any guidance would be appreciated.

Thanks in advance,

Ben

 

[rharmelink]

I am trying to calculate a MA in Excel (or OO Calc) but (and it's a big but) the MA period is a variable equal to the value of a cell.

Just use the OFFSET() function. For example:

=AVERAGE(OFFSET(A1,0,0,50,1))

Tells EXCEL to compute a moving average starting from cell A1 and extending for 50 rows and 1 column. Just substitute your cell reference for the 50 and you've got it.

I do this all the time so it's easy to change the moving average without changing all of the formulas -- just change the cell that tells it what period to use.

 

[RedGreenBen]

Just use the OFFSET() function.

Brilliant Many thanks.

Ben