Create a 12 month rolling average

[forest8]

I have a data sheet with 20 columns that represent 20 months of data (starts
at column b). Monthly data is added for each month. I want to create a
rolling average. The data starts at Row 3 and ends at row 737.

E.g. for Dec 2007, the rolling average would be Jan 2007 to Dec 2007.
e.g. for Jan 2008, the rolling average would be Feb 2007 to Jan 2008, etc,

Thanks

 

[Max]

Assuming data for Jan07 to Aug08 will be entered in cols B to U (20 cols),
data from row3 down

Try in say, W3: =AVERAGE(OFFSET(B3,,,,12))
Copy W3 across by 20 cols to AP3, fill down as far as required

 

[joeu2004]

I have a data sheet with 20 columns that represent 20 months of
data (starts at column b).  Monthly data is added for each month.
 I want to create a rolling average.  The data starts at Row 3 and
ends at row 737.

Does the following work for you?

Suppose you want the rolloing average in row 2. In M2 (12th month),
enter the following:

=if(count(B3:M737)=0, "", average(B3:M737))

Copy that left through U2 (20th month).

 

[forest8]

Hi there

Thank you both but neither of these worked.

I want this rolling average to always remain in the same column (e.g. Column
AB). As I add each month, the rolling average will change accordingly.

Thanks
J

 

[Max]

This might suffice ..

In AB3, copied down:
=AVERAGE(OFFSET(B3,,COUNT(B3:U3)-1,,-12))

The above presumes each cell within the 20 col range (B to U) will be
sequentially filled each month from left to right with numbers (there should
be no blank cells in between)

 

[Max]

If there could be blank cells in-between within the 20 col range (B to U) in
the sequential fill each month from left to right

Place in AB3, array-entered, copied down:
=AVERAGE(OFFSET(B3,,MAX((B3:U3<>"")*(COLUMN(B3:U3)))-2,,-12))

---

 

[forest8]

Hi Max

The revised formula worked but you're correct in thinking that I do have
blank cells/columns. Unfortunately, the second formula didn't work.

J

 

[forest8]

What I meant to say was that I get "#Value" when I used the last formula.

J

 

[Max]

What I meant to say was that I get "#Value" when I used the last formula

You got the error because you didn't "array-enter" the formula
To "array-enter" means to press CTRL+SHIFT+ENTER [CSE] in confirming the
formula (instead of just pressing ENTER). If you did this confirmation
correctly, you should see Excel wrap curly braces: { } around the formula in
the formula bar. If you don't see it, click inside the formula bar and try
the CSE again.

That said, I'd suggest a tweak to the earlier array formula - that we
control/fix the range to be averaged based on the col labels row (row2)
instead of the data rows, as you may have no data for entry for a certain
current month, in which case Excel would not know that the "current" rolling
range has moved 1 col to the right.

So, assuming that you would be entering the col labels in row2 progressively
each month from left to right within the range B2:U2

Put in AB3, array-enter the formula, copied down:
=AVERAGE(OFFSET(B3,,MAX(($B$2:$U$2<>"")*(COLUMN($B$2:$U$2)))-2,,-12))

---

 

[forest8]

It worked with CSE but I have another question:

Can I use this formula if I only want to create a rolling average based on
the 12 most recent months (even if it includes blanks)?

This formula seems to take 12 months regardless of whether it's the 12 most
recent months.

What I meant to say was that I get "#Value" when I used the last formula

You got the error because you didn't "array-enter" the formula
To "array-enter" means to press CTRL+SHIFT+ENTER [CSE] in confirming the
formula (instead of just pressing ENTER). If you did this confirmation
correctly, you should see Excel wrap curly braces: { } around the formula in
the formula bar. If you don't see it, click inside the formula bar and try
the CSE again.

That said, I'd suggest a tweak to the earlier array formula - that we
control/fix the range to be averaged based on the col labels row (row2)
instead of the data rows, as you may have no data for entry for a certain
current month, in which case Excel would not know that the "current" rolling
range has moved 1 col to the right.

So, assuming that you would be entering the col labels in row2 progressively
each month from left to right within the range B2:U2

Put in AB3, array-enter the formula, copied down:
=AVERAGE(OFFSET(B3,,MAX(($B$2:$U$2<>"")*(COLUMN($B$2:$U$2)))-2,,-12))

---

 

[Max]

Can I use this** formula if I only want to create a rolling average based

on
the 12 most recent months (even if it includes blanks)?

**That's exactly what the formula I gave does when you enter the col labels
in row2 progressively each month from left to right within the range B2:U2.

**Put in AB3, array-enter the formula, copied down:
=AVERAGE(OFFSET(B3,,MAX(($B$2:$U$2<>"")*(COLUMN($B$2:$U$2)))-2,,-12))

The "MAX(...)-2" part of it in the OFFSET anchors/fixes the rightmost
startpoint, ie the most "recent" col (col param) while the -12 (width param)
then grabs the 12 cols range to the left of this anchor col. The minus
in -12 means to the left.

If you carefully select just the OFFSET(...) part of it within the formula
bar and then press F9 to evaluate, the 12 "recent" cols range will be
revealed. Use this as a visual check.

---

 

[forest8]

Hi again

When I verified this formula, I had a problem.

The earliest data is in column B and the latest is in V. The next column to
be filled is in W. This formula doesn't work well if Column W is left blank.

I've attached a sample from my file.

Months Category 1 Category 2 Category 3
Apr 06 438 2429 187
May 06 468 2509 188
Jun 06 386 2356 115
Jul 06 439.99 2494.934
Aug 06 418.738 2623.68
Sep 06 476.762 2818.631
Oct 06 384.501 2800.796
Nov 06 500.444 2805.878 1286.587
Dec 06 411.816 2857.546 1048.737
Jan 07 595.142 3119.351 1357.796
Feb 07 440.891 3119.204 1611.12
Mar 07 548.954 3349.091 1877.482
Apr 07 501.714 3103.273 1369.555
May 07 526.133 2728.437
Jun 07 536.357 2538.707
Jul 07 485.514
Aug 07 457.713
Sep 07 404.142
Oct 07 459.103
Nov 07 418.293
Dec 07 350.3


"12 month
rolling" 477.0213333 2863.294 1106.772375
Check 477.0213333 2993.0105 1553.98825

J

 

[Max]

Is this a new issue? I haven't a clue what's going on. In this thread, the
layout discussed was month headers labelled across the row in B2, C2, ...
etc.

Suggest you put in a fresh, new posting, and in that post,
you could also upload & post a direct link to your sample**,
using:
http://www.freefilehosting.net/
**desensitize it as appropriate

---

 

[forest8]

It's the same issue. I put the sample data into this format so that you can
see that my 12-month rolling average and the check average are not producing
the same result.

 

[forest8]

Ignore my last post. I did the F9 check and discovered that it's not picking
up the 12 most recent cells but only 12 cells that have data.

 

[Max]

a. Let's back it up a bit, in my earlier response, I mentioned:
---------------
... That said, I'd suggest a tweak to the earlier array formula - that we
control/fix the range to be averaged based on the col labels row (row2)
instead of the data rows, **as you may have no data for entry for a certain
current month, in which case Excel would not know that the "current" rolling
range has moved 1 col to the right**

So, assuming that **you would be entering the col labels in row2
progressively each month from left to right within the range B2:U2**

Put in AB3, array-enter the formula, copied down:
=AVERAGE(OFFSET(B3,,MAX(($B$2:$U$2<>"")*(COLUMN($B$2:$U$2)))-2,,-12))
----------------

Pl note the 2 emphasis parts above enclosed within **

Can you confirm that the above has been applied to what you have over there?

b. I'm not able to decipher what's going on from your sample data as posted.
You need to post a link to your file with the sample data, as suggested.

---