How do I create a moving average in Excel?

[Guest]

I am trying to create a 12 month rolling average, example follows.

J F M A M J J A S O N D average
item 1 2 3 4 5 6 7 8 9 10 11 12 (rolling average)

thank you

 

[Guest]

In your example a rolling 12 month average is the same thing as the average
-- given that you have only listed 12 months. Assuming your values are in
A1-L1, you could put =Average(A1:L1).

Art

 

[RagDyer]

When you say "rolling", do you mean:

Case #1
1 to 12 - Jan to Dec
2 to 13 - Feb to Jan
3 to 14 - Mar to Feb

OR
Case #2
1 to 12 - Jan to Dec-04
13 to 24 - Jan to Dec-05
25 to 36 - Jan to Dec-06

With data in Column A:

Case #1
=AVERAGE(A1:A12)
And copy down as needed.

Case #2
=AVERAGE(INDEX(A:A,12*ROWS($1:1)-11):INDEX(A:A,12*ROWS($1:1)))
And copy down as needed.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Guest]

Art,
What I detailed was the first sequence.

example 1
1/2005 through 12/2006
2/2005 through 1/2006
etc.

thank you for your response.

 

[Guest]

RagDyer,
I guess I should have used a better example, I was looking for something
that would provide the average as follows:

example 1
1/2005 through 12/2005 and then when 1/2006 got populated the average would
be of 2/2005 through 1/2006 and keep rolling as I added more data.

Thank you for your response.

 

[Guest]

If I understand you correctly, perhaps this is what you are looking for:

2005 2006
J F M A M J J A S O N D J F M A M
1 4 5 2 7 9 9 3 4 2 1 7 3 2 3 8 5

Under the D: Average(A1:A12)
Under the J: Average(A2:A13)
Under the F: Average(A3:A14)

and so on.

Art

 

[Biff]

Hi!

Assumes your data to average is in row 1 and starts in cell B1 *and* there
are at least 12 values to average.

=AVERAGE(OFFSET(B1,,COUNT(1:1)-1,,-12))

If you might not have 12 entries or there might be empty cells or you might
want to exclude any 0 values......then it gets much more complicated!

Biff

 

[RagDyeR]

So, I now read that you want a *single* cell to display the average of the
*last 12* entries in a column ... right?

With data in A1 to A100, try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<>""),12)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

NOW ... this will average the bottom (last)12 numbers in A1 to A100,
BUT ... If you have *less* then 12, it will *still* return the average for
any values entered.

*Caveat*
It will *not* count *empty* cells to be included in the 12 cells to average.
SO, if you have no values for a particular month, you'll have to enter a
zero in that cell so it can be included in the average.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================





RagDyer,
I guess I should have used a better example, I was looking for something
that would provide the average as follows:

example 1
1/2005 through 12/2005 and then when 1/2006 got populated the average would
be of 2/2005 through 1/2006 and keep rolling as I added more data.

Thank you for your response.

 

[Guest]

Art,
Yes, that's what I'm trying to accomplish.

thanks.

 

[Guest]

Thank you for the quick response, this sounds like a good answer even if I
have to use an array