| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rolling totals | Excel Worksheet Functions | |||
| Rolling 13 Month Charts | Charts and Charting in Excel | |||
| Pivot Tables - Rolling Totals - % Change | Excel Discussion (Misc queries) | |||
| How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) | |||
| maximum of rolling totals - aka Rmax | Excel Worksheet Functions | |||
Threaded Mode
