Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving average | Excel Worksheet Functions | |||
moving average | Excel Worksheet Functions | |||
how to do a 50 day and 200 day moving average | Excel Discussion (Misc queries) | |||
Moving average | Charts and Charting in Excel | |||
Moving average | Excel Worksheet Functions |