Simple Moving Average with N

[Casius]

Hi,
I'm trying to create a Simple Moving Average coloum with "N" days
data. Is it possible enter the varible in a cell and have excel
calculate the moving average with the cells N value?
In other words, is it possible to create a S.M.A coloum where you
can change the number of periods with just one cell change?

Thanks in advance for any help..

Steve

 

[Guest]

Casius

Try this:-

=AVERAGE(OFFSET(A1,COUNT(A:A)-G1,0,G1,1))

Put your data in Column A and the amount of data points you want to average
in G1 there must always be more data points than the number in G1. As you add
numbers to the bottom of column a it will average the last N values.

Mike

 

[Jon Peltier]

This computes only a single value. More useful would be a running moving
average.

Assuming data in column A starting in A2, and the number of points to
average in G1, enter this into cell B2 and fill down as far as there is data
in column A:

=AVERAGE(OFFSET(A2,1-MIN($G$1,ROW()-1),0,MIN($G$1,ROW()-1),1))

- Jon

 

[Casius]

This computes only a single value. More useful would be a running moving
average.

Assuming data in column A starting in A2, and the number of points to
average in G1, enter this into cell B2 and fill down as far as there is data
in column A:

=AVERAGE(OFFSET(A2,1-MIN($G$1,ROW()-1),0,MIN($G$1,ROW()-1),1))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

Many thanks to you both for your help, sorry to be so stupid but how
would i reverse the formula to calculate going up a row instead of
down. I.E I have the oldest data in row A349 and would like to work up
to A2 for todays data. Once again sorry for not understanding this.

 

[Casius]

Many thanks to you both for your help, sorry to be so stupid but how
would i reverse the formula to calculate going up a column instead of
down. I.E I have the oldest data in column A349 and would like to work up
to A2 for todays data. Once again sorry for not understanding this.

 

[Jon Peltier]

=AVERAGE(OFFSET(A2,0,0,$G$1,1))

- Jon

 

[Casius]

=AVERAGE(OFFSET(A2,0,0,$G$1,1))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

Thanks so much Jon Peltier, That is just what I was after. )
Love your webpage by the way.