Simple Moving Average with N

  • Thread starter Thread starter Casius
  • Start date Start date
C

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
 
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
 
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
 
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
_______
Click to expand...


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.
 
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.
Click to expand...
 
=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
_______
Click to expand...



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