How to Calculate Moving Average for Different Period

2horses

New Member
Joined
Mar 22, 2012
Messages
3
Hi all,

I would like to use Excel to calculate the Moving Average for Dow Jones or SP500 in Excel.

For example I have a set of data, said 500 data in Column A, when I entered a different period, say 5 or 10, in B1, can Excel give Moving Average in Column C for 5 day Moving Average or 10 day Moving Average?

Thx.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board..

don't think we have quite enough information to answer completely..
But the offset function may be of some help..

this will average from A1 to whichever cell depending on number entered in B1

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

So if B1 is 10, it does A1:A10
if B1 is 15, it does A1:A15
Etc..


But this doesn't vary the beginning point (A1)..


Hope that helps.
 

darkyam

New Member
Joined
Apr 7, 2008
Messages
17
=Average(Index(A:A,Match(99^99,A:A)+1-B1):Index(A:A,Match(99^99,A:A))) should do it, where B1 has the number of days. Of course, this assumes there are no totals or any other numerical data below your table in column A. I'm assuming your data is piled with earliest on top. If not, you can use, jonmo1's formula or, to avoid the potential pitfalls of Offset, =Average(A$1:Index(A:A,B1))
 

2horses

New Member
Joined
Mar 22, 2012
Messages
3
Thx for your help.

Maybe I should explain my question more.

You're right, when I enter 10 in B1, then the Excel should give the average of A1 to A10, but that is Moving Average. Thus.....

if I enter 10 in B1, Excel should return answer from C10 (to C????) to give its answer of Moving Average of A1 to A10; if I entered 5 in B1, Excel should return answers from C5 (to C????) to give Moving Average of A1:A5.

is this small piece explanation help?
 

Ali Kırksekiz

Board Regular
Joined
Jul 20, 2006
Messages
108
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">65</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">99</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">37,8</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">912</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">53</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sayfa1


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C1)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C2)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C3)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C4)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C5)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C6)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C7</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C7)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C8</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C8)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C9</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C9)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C10</TH><TD style="TEXT-ALIGN: left">=IF(ROWS($C$1:C10)=$B$1,AVERAGE($A$1:INDIRECT("A"&$B$1)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

2horses

New Member
Joined
Mar 22, 2012
Messages
3
Jon, Darkyam, Ali, thx.

You guys are so helpful.:)

Ali,

According to your example, if I enter 5 in B1, C5 should return the latest FIVE data average A1:A5. But since it is a Moving Average, then C6 should return also the latest FIVE data average A2:A6 and so C7 for average of A3:A7.

Whereas if I enter 10 in B1, C10 should return the latest TEN data average A1:A10, and C11 should return average of A2:A11, and so on.

thx.

2horses
 

Watch MrExcel Video

Forum statistics

Threads
1,100,205
Messages
5,473,145
Members
406,849
Latest member
AndreasMs

This Week's Hot Topics

Top