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/community/en-us/default.mspx?mid=14abdad9-a97b-4d3a-bd63-a3bef08b5f14&dg=microsoft.public.excel.programming
0
Utf
3/3/2010 4:38:07 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1125 Views

Similar Articles

[PageSpeed] 57

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/community/en-us/default.mspx?mid=14abdad9-a97b-4d3a-bd63-a3bef08b5f14&dg=microsoft.public.excel.programming
0
Utf
3/3/2010 8:51:01 PM
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/community/en-us/default.mspx?mid=14abdad9-a97b-4d3a-bd63-a3bef08b5f14&dg=microsoft.public.excel.programming
0
Utf
3/3/2010 9:07:05 PM
Reply:

Similar Artilces:

Custom format #3
To format a custom format for credit cards I tried 0000-0000-0000-0000 and it looks good like it should work. Somehow the last digit changes to 0 no matter what number I put in. Yes have tried it several times, on sheet the church sent me and again trying it on Excel 2003. Weird problem always ends in 0 ?? On Mon, 25 May 2009 12:56:59 -0400, "John McCabe" <jlmccabes@cfl.rr.com> wrote: >To format a custom format for credit cards I tried > 0000-0000-0000-0000 and it looks good like it should work. >Somehow the last digit changes to 0 no...

3 leaflets in landscape on an A4 page. Any suggestions?
Microsoft has wonderful templates. Love them. Can't find a leaflet back and front which is on an A4 page which requires only 2 slices to make it in to 3 leflets. Max usage of paper, minimum cost and minimum environmental cost. Start with something close and modify it. Or remember that not everything needs to be started with a template. Creativity goes a long way. -- JoAnn Paules MVP Microsoft [Publisher] "Rosemarie" <Rosemarie@discussions.microsoft.com> wrote in message news:777EFC05-28DA-45A1-A6FA-687400391573@microsoft.com... > Microsoft has wonderful...

mail delivery #3
i just install new echange 2003 ent. to existing 2000 ad where is exchange 2000 standard. In my test enviroment all mail delivery succees in new server, but when i do exactly same in production, those one's which are in exchange 2003 they can't get any mails from "old server" or outside our corporate. They can send mail to users which mailboxes are inside our corporate, and they received mails from new servers. ...

WU hangs on 3 of 3
Fresh SBS 2008 install, installed SP2 afterwards, reboot all ok, then executed windows update with all criticals, it downloads and installs succesfully. After reboot it starts windows with "configuring updates x of 3, don't turn off your computer" and the server hangs there on the "3 out of 3" on 0%, there's no lan or disk activity at all and the progress circle just keeps running. This already happened yesterday with a canceled WU(before installing SP2) and we had to format and reinstall thanks to this problem because safe mode worked but after th...

Help With Grand Total for Subreports
I have three subreports, with totals, that I have pulled into one main report. The totals for the three subreports are working fine, but I can't get them to sum to a grand total in the report footer. Can someone please tell me how to do this? ridgerunner wrote: >I have three subreports, with totals, that I have pulled into one main >report. The totals for the three subreports are working fine, but I can't >get them to sum to a grand total in the report footer. The report footer text box can use an expression like: =report1.Report.total1 + report1.Repor2.total2 + r...

Windows Sharepoint Services 3.0 Migration.
Hello, I am in a bit of trouble here and need some help. We have a Windows 2003 Standard server (X86) running SQL 2005 and SharePoint Services 3.0. We need to migrate the SharePoint Services 3.0 to a Windows 2008 R2 server running SQL 2005 X64. The server name will be different than the original server. Since we have the server name, the operating system version and the processor architecture changing I am looking for clear instructions on how to migrate the SharePoint Application. I read a lot websites including this one here http://technet.microsoft.com/en-us/library/cc5...

Sort creating total errors
I have a spreadsheet with about 20 columns and 8,000 rows. I have numerical data in most of the columns corresponding to people. Like this: John 9 6 Debbie 9 5 James 8 5 Total 26 16 If I highlight all columns and do a sort by the person's name, the total number at the bottom changes even if the data has not changed. Do you know why this is happening? Thanks in advance for any help! What are the formulas you used in those total cells? excel idiot wrote: > > I have a spreadsheet with about 20 columns and 8,000 rows. I have numerica...

math #3
How do I get the average of a group of years. Like 1999, 1998, 2000, etc. I think you need to provide much more detail to get a useful reply. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bill" <Bill@discussions.microsoft.com> wrote in message news:044868EC-8884-4E40-9F93-EE9C5EFABCFA@microsoft.com... > How do I get the average of a group of years. Like 1999, 1998, > 2000, etc. maybe something like =AVERAGE(ROW(INDIRECT("1998:2005"))) as an array formula, so commit with Ctrl-Shift-Enter -- ...

Duplicate look_up values in VLOOKUP #3
Hi all, I've got a VLOOKUP formula functioning in the usual way. However, sometimes the look_up value is duplicated. When this occurs VLOOKUP always returns the first instance of the entry. Is there a means to get Excel to ignore entries it has already returned an entry for? The VLOOKUP is part of a bigger formula, being: =IF(AND('Dependency Log'!$A2=Matrix!$B3, 'Dependency Log'!$E2=Matrix!D$2),(VLOOKUP($B3,look_up,2,FALSE) ),"") Any ideas? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Prof...

Upgrade #3
Hi Folks; If I am running MS Office 2000 on my XP Home Edition box can I upgrade just the Outlook portion of Office to Outlook 2003 or Outlook XP ????????????????? TIA Later, Malcolm On Sat, 19 Nov 2005 07:38:57 GMT, Malcolm <malcolm34465@yahoo.com> wrote: > >If I am running MS Office 2000 on my XP Home Edition box can I upgrade >just the Outlook portion of Office to Outlook 2003 or Outlook XP >????????????????? > Sure. Just know that you will not be able to use Word as your message editor in Outlook and you will not be able to use Word in a mail merge. -- <<&...

CCheckListBox #3
Dear Charles As you suggested to my question posted in ADO.Net news group I added the implimentation of the Create() function of CCheckListBox class. but it is still giving following error: error LNK2001: unresolved external symbol "public: virtual struct CRuntimeClass * __thiscall CMyCheckListBox::GetRuntimeClass(void)const " (?GetRuntimeClass@CMyCheckListBox@@UBEPAUCRuntimeClass@@XZ) Thanks Manjree "Manjree Garg" <garg@newsgroup.nospam> ha scritto nel messaggio news:F59BE84D-88CA-4994-8F1F-D6888AD8D45E@microsoft.com... > As you suggested to my qu...

Help monthly sales by person
Need formula to figure amount of sales per person in a given month. columns currently are: Inv#, Date, Sale, Net $, Salesman # Need formula for monthly sales per Salesman # Thank you -- Htoomuc Posted from - http://www.officehelp.i With your headers in Row1, from Columns A to E, And the datalist from A2 to E100, Enter the month and year to lookup in F1 (mm/yy), And the salesman's # in F2, And try this: =SUMPRODUCT((TEXT(B2:B100,"mm/yy")=TEXT(F1,"mm/yy"))*(E2:E100=F2)*D2:D100) -- HTH, RD --------------------------------------------------------------------------...

more than 3 conditional formating in excel
Hi I am new to conditional formating in Excel. In row 2 I need to enter nos. between 1-5. I want each color to have a particular color. I have managed to do 4 (3 with conditional formating and the 4th retaining the default color). Is there a way i can do all 5 colors? Thanks You need to use VBA. Try this:- Private Sub Worksheet_Calculate() 'Code must be placed in the codemodule of the actual sheet you are working with. Dim oCell As Range For Each oCell In Range("A1:A20") Select Case oCell.Value Case Is < 1 oCell.Interior.Colo...

OWA #3
Hi , we have the following setup and would like advice as to what others are doing. Internet-->Router->Cisco Pix-->(Internal Network->Exchange Srv) We would like to publish our Outlook Web Access the most secure way possible. We do not want to put a Windows 2k box in the DMZ or outside or Network to use as a front end server. Could we do the following: Internet-->Router->Cisco Pix with port 443 mapped to FrontEndSrv->(Internal Network->FrontEndSrv Exchange with tight security providing OWA for Internal Exchange server)...and of course ssl cert on the Exch box....

Macro Help #3
I turn to you guru's for help with a macro question. I have to worksheets in my workbook. Both have a coulmn that has a 6 digit number in each cell (an invoice number) What I need to do is have a macro that starts with the first invoice # in sheet 1, searches for it in sheet 2. If it finds it, delete that row from Sheet 2. Then it will look at the 2nd invoice number on sheet 1, Search from it in Sheet 2 If it finds it, delete that row from Sheet 2. etc, etc, etc so, my question to you is..... How? Modify this to suit. Sub Deletematches() On Error Resume Next For Each cel In [c14:...

Event id 1000 #3
Hello, I have office xp installed on pc with profing tools and sometimes when using outlook reply message, crash and open word with recouvering document. the error on event viewer is event id 1000 source microsoft office 10 . Please can help me! Thanks in advance. ...

Disclaimer #3
Hi We run Win2003, Exchange2003 and I would like to add a disclaimer on all outgoing emails. Is there an other way then buying a Add-on tool? If not what is good tool? Thanks Roman Sure. You can take a look at this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;317680 or this http://support.microsoft.com/default.aspx?scid=kb;EN-US;317327 -- Martin Blackstone MVP - Exchange http://www.swinc.com/resource/exchange.htm http://www.swinc.com/resource/e2kfaq_appxc.htm "Roman" <roman@schwabbrothers.com> wrote in message news:OUj1pkBoEHA.4008@TK2MSFTNGP14.phx.gbl....

Resource booking #3
Hi everyone, I can't book an resorce , it always says that it is recurring and I can't change recurrence. Thanx in advance. And is the meeting you are organizing a recurring meeting? A bit more details would be nice here, such as how you have set up the resource mailboxes, what version of Exchange, and so forth. -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "Peter" <Peter@discussions.microsoft.com> wrote in message news:03C60C8B-8E7E-4A6A-9E68-1EC160315027@microsoft.com... > H...

how do i make 1/3 look like a fraction
i can get 1/4 and 1/2 to look like a fraction but the rest (1/3 1/8 1/16) don't happen There are a couple of possibilities - see: http://www.gmayor.com/createfraction.htm or http://www.word.mvps.org/FAQs/Formatting/CreateFraction.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<...

automatic tally for a total score
I am an English teacher and I have created a "grading rubric" for an essay assignment in my class. This rubric has 20 criteria in rows on which the students' work is to be graded, such as "Grammar," "Spelling," etc. There are five columns next to each criteria, each designating the score that the student should receive for each criteria - Column #1 designates a full five point score for that criteria, Column #2 designates a four point score in that criteria, etc. What I want to do is place a radio button or a check box in each table cell that, whe...

CRM 3.0 Installation Issue 02-01-06
I have tried installing CRM 3.0 3 times on a Windows 2K server, IIS 5, .NET 1.1, SQL Server 2K w/SP 4, with all updates and patches. Everytime I install it ends with the same error. When I attempt to loogin into the admin page I get : Authentication Error Microsoft CRM could not log you on to the system. Make sure your user record is enabled and that you have been assigned at least one security role. For more information, contact your system administrator. I am not sure where to go with this now. Can anybody help? ...

Free Busy information #3
Free busy information for Exchange 5.5 not visible in Exchange 2003 in mix mode. Exchanhe 2003 free busy information not visible in different admin groups either. Why is this happening?? You need to configure the free/busy folder to replicate to the different admin groups. For more information and some topology descriptions, see "Working with the Exchange Server 2003 Store" (http://go.microsoft.com/fwlink/?LinkId=33049). Chapter 6 deals with free/busy. -- Teresa Appelgate Exchange User Education ****** Disclaimers ****** This posting is provided "AS IS" with no warran...

How do I add a total to a stacked bar graph in Excel
I am using Excel for some graphs in Powerpoint and we are using stacked bar graphs. Does anyone know how to add a total on a stcaked graph? I can add individual values but I am looking for total per bar. the simplest way is to copy the data with the totals into the chart. select the section of the bar equal to the totals and change the axis to secondary add the series values and in paterns select none for fill and line. in th elegehgend select the line for the totals and clar all. "Greg L" wrote: > I am using Excel for some graphs in Powerpoint and we are using stacked bar...

Totalling numbers that are Conditionally Formatted
Is there any way to total up a column by the colours of a cell? I have a column that has Green, Yellow, Amber and Red cells and I need to add up how many of each are there and then calculate the percentage. Any help would be gratefully received! Ann In article <6B551016-3ECC-4412-8DE5-AEB5A908141B@microsoft.com>, Ann Knoff <Ann Knoff@discussions.microsoft.com> wrote: > Is there any way to total up a column by the colours of a cell? > > I have a column that has Green, Yellow, Amber and Red cells and I need to > add up how many of each are there and then calculat...

Rounding Off #3
$248,071 $260,290 $261,640 $106,316 $111,553 $112,131 $0 $0 $0 $354,387 $371,843 $373,771 I need to enter all of the above numbers into a form that looks like this: xx 0,000 So, I have to enter the first two didgits. I need to round off the rest. The first row would be: $250,000 $260,000 $260,000 How would I construct a formula to do that? Thanks If you enter the 2 digits in A1:Axxx, put this formula in B1 =A1*10000 and drag the formula down through Bxxx. Then format the cells as currency with 0 places of decimal. If you enter the...