Moving Average

[Elton Law]

Hi Expert,
I think you have heard this term before in Finanical market- technical
analysis.
I have a series of stock price data.
Is it possible that say I put a number 6 in a cell and then it can sum 6
rows data and then divide by 6 too.
Another example .... I put 9 in cell A1.
Stock price series data in B1 to B20 ... it will sum B1 to B9 and then
divide by 9 too.
Look like it is conditional sum depending on the number in a cell.
Thanks

 

[Jacob Skaria]

Try the below to sum B1 to the number of rows specified in A1

=SUM(OFFSET(B1,0,0,A1,1))

If this post helps click Yes

 

[Mike H]

Hi,

=SUM(B1:INDEX(B1:B20,A1))/A1

Mike

 

[Jacob Skaria]

'to average
=AVERAGE(OFFSET(B1,0,0,A1,1))

If this post helps click Yes

 

[Elton Law]

Hi Jacob,
It works .. Thanks so much.
Regards, Elton

 

[Elton Law]

Hi Jacob,
I have column problem.
Say the column format is like this.
C1 is 6
Then I can work out the MA depending on the number in C1.
Your formula looks like it is meant for forward calculation.
Mine is backward from the latest date (current price) to the rows depending
on the number in cell C1.
Can you make it please? Thanks indeed.

Column A Column B Column C
Date Price 6
13/10/2009 0.455
14/10/2009 0.46
15/10/2009 0.455
16/10/2009 0.54
19/10/2009 0.51
20/10/2009 0.55
21/10/2009 0.56
22/10/2009 0.54 Moving average (15-22 Oct if it's 6-day MA)
23/10/2009 0.55 Moving average (16-23 Oct if it's 6-day MA)
27/10/2009 0.53 Moving average (19-27 Oct if it's 6-day MA)

 

[Jacob Skaria]

Elton, thanks for your feedback. With your data try the below

=AVERAGE(OFFSET(B1,COUNTA(B:B),0,-C1-1,1))

If this post helps click Yes

 

[Elton Law]

Hi Jacob,
It only works for the last row of data.
The second last and the third last did not work.
What should I do please?
I have tried Mike one ... does not work either ....
Thanks
Elton

 

[Elton Law]

Hi all,
I tried to amend and use
=Sum(Offet(B20,0,0,-C1,1))/C1
Work now ... Thanks for all.

B20 means start from the bottom cell ....
Anyway, thanks