3 month rolling totals
 

Hello,

I just typed this in and the message didn't take - I hope this is not
duplicated.

I have inherited a spreadsheet with 3 month rolling totals. Is there a way
to automate this?

Sample:
Widget(A1) Jan(B1) Feb(C1) Mar(D1) TOTAL(E1)
Where TOTAL = sum(b1:d1)

Next month, we add Apr(E1) moves TOTAL to (F1) and doesn't compute Jan(B1)
Where TOTAL = sum(c1:e1)

There are many rows with this type of data and so I would rather not
copy/paste/reformat the formula.

Thanks in advance!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

3 month rolling totals
 

Try something like this
A B C D E
1 Jan Feb Mar Apr Tot
2 1 2 3 4 9

The formula for E2 is =SUM(OFFSET(E2,0,-3,1,3))

This defines a range 1 row and three columns wide that always starts 3
columns to the left of the cell with the formula, so when you add a column
(like May) the total formula still references the correct 3 cells.
--
If this helps, please remember to click yes.


"DTTODGG" wrote:

Hello,

I just typed this in and the message didn't take - I hope this is not
duplicated.

I have inherited a spreadsheet with 3 month rolling totals. Is there a way
to automate this?

Sample:
Widget(A1) Jan(B1) Feb(C1) Mar(D1) TOTAL(E1)
Where TOTAL = sum(b1:d1)

Next month, we add Apr(E1) moves TOTAL to (F1) and doesn't compute Jan(B1)
Where TOTAL = sum(c1:e1)

There are many rows with this type of data and so I would rather not
copy/paste/reformat the formula.

Thanks in advance!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

3 month rolling totals
 

Thank you Paul ... simple and beautiful!

"Paul C" wrote:

Try something like this
A B C D E
1 Jan Feb Mar Apr Tot
2 1 2 3 4 9

The formula for E2 is =SUM(OFFSET(E2,0,-3,1,3))

This defines a range 1 row and three columns wide that always starts 3
columns to the left of the cell with the formula, so when you add a column
(like May) the total formula still references the correct 3 cells.
--
If this helps, please remember to click yes.


"DTTODGG" wrote:

Hello,

I just typed this in and the message didn't take - I hope this is not
duplicated.

I have inherited a spreadsheet with 3 month rolling totals. Is there a way
to automate this?

Sample:
Widget(A1) Jan(B1) Feb(C1) Mar(D1) TOTAL(E1)
Where TOTAL = sum(b1:d1)

Next month, we add Apr(E1) moves TOTAL to (F1) and doesn't compute Jan(B1)
Where TOTAL = sum(c1:e1)

There are many rows with this type of data and so I would rather not
copy/paste/reformat the formula.

Thanks in advance!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming