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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"forest8" wrote:
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

On Feb 13, 2:00*pm, forest8 wrote:
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).

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

"joeu2004" wrote:

On Feb 13, 2:00 pm, forest8 wrote:
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).

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"forest8" wrote in message
...
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

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

J

"forest8" wrote:

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

"joeu2004" wrote:

On Feb 13, 2:00 pm, forest8 wrote:
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" wrote:
What I meant to say was that I get "#Value" when I used the last formula
Place in AB3, array-entered, copied down:
=AVERAGE(OFFSET(B3,,MAX((B3:U3<"")*(COLUMN(B3:U3) ))-2,,-12))

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

"Max" wrote:

"forest8" wrote:
What I meant to say was that I get "#Value" when I used the last formula
Place in AB3, array-entered, copied down:
=AVERAGE(OFFSET(B3,,MAX((B3:U3<"")*(COLUMN(B3:U3) ))-2,,-12))

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---