Simple Moving Average with N
 

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

Simple Moving Average with N
 

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

"Casius" wrote:

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

Simple Moving Average with N
 

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
_______


"Mike H" wrote in message
...
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

"Casius" wrote:

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

Simple Moving Average with N
 

On 18 May, 14:30, "Jon Peltier"
wrote:
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
_______

"Mike H" wrote in message

...

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

"Casius" wrote:

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


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.

Simple Moving Average with N
 

On 19 May, 07:40, Casius wrote:
On 18 May, 14:30, "Jon Peltier"
wrote:



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
_______

"Mike H" wrote in message

...

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

"Casius" wrote:

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

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.

Simple Moving Average with N
 

=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
_______


"Casius" wrote in message
ups.com...
On 18 May, 14:30, "Jon Peltier"
wrote:
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
_______

"Mike H" wrote in message

...

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

"Casius" wrote:

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


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.

Simple Moving Average with N
 

On 19 May, 20:49, "Jon Peltier"
wrote:
=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
_______

"Casius" wrote in message

ups.com...

On 18 May, 14:30, "Jon Peltier"
wrote:
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
_______

"Mike H" wrote in message

...

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

"Casius" wrote:

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

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.



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