Excel expertise... calculating moving averages.

RedGreenBen

Well-known member
Messages
268
Likes
52
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
 
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.
 
Top