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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
=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))
 
Upvote 0
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?
 
Upvote 0
<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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,462
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top