How to Calculate Moving Average for Different Period

2horses · Mar 22, 2012

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.

Jonmo1 · Mar 22, 2012

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 · Mar 22, 2012

=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 · Mar 22, 2012

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 · Mar 23, 2012

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 · Mar 23, 2012

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

How to Calculate Moving Average for Different Period

2horses

New Member

Excel Facts

Jonmo1

MrExcel MVP

darkyam

New Member

2horses

New Member

Ali Kırksekiz

Board Regular

2horses

New Member

Similar threads

Forum statistics

Share this page

How to Calculate Moving Average for Different Period

2horses

New Member

Excel Facts

Jonmo1

MrExcel MVP

darkyam

New Member

2horses

New Member

Ali Kırksekiz

Board Regular

2horses

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock