1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel - running total per week

Discussion in 'Windows & Other Software' started by PaulStat, 10 Sep 2009.

  1. PaulStat

    Soldato

    Joined: 1 Mar 2003

    Posts: 5,437

    Location: Cotham, Bristol

    Say I have a number of entries in a spreadsheet, each entry represents a day, one of the columns for that entry represents a running total for a week. How do I calculate the running total of a specific column for a week?

    Thanks,
    Paul
     
  2. PermaBanned

    Capodecina

    Joined: 7 Jul 2009

    Posts: 16,239

    Location: Newcastle/Aberdeen

    Click on cell where that column finishes and click autosum?
     
  3. PaulStat

    Soldato

    Joined: 1 Mar 2003

    Posts: 5,437

    Location: Cotham, Bristol

    But this is something I'm going to update constantly, for example below, the first column is the date, then the value for that day, then the running total for the week

    07/09/09 1 1
    08/09/09 2 3
    09/09/09 3 6
    10/09/09 4 10
    11/09/09 5 15
    12/09/09 6 21
    13/09/09 7 28
    14/09/09 1 1
    ...

    And so on
     
  4. Dolph

    Man of Honour

    Joined: 17 Oct 2002

    Posts: 48,408

    Location: Plymouth

    For each cell use the above cell and add the daily total. in your example above, but this in C3

    =C2+B3

    then in C4

    =C3+B4

    And so on.

    That should work just fine unless the spreadsheet gets huge.
     
  5. PaulStat

    Soldato

    Joined: 1 Mar 2003

    Posts: 5,437

    Location: Cotham, Bristol

    But that doesn't reset it for each consecutive week though? i.e. the running total resets every monday
     
  6. moid

    Gangster

    Joined: 27 Jun 2004

    Posts: 459

    Location: Darlington - UK

    Just need an IF statement

    =if([previous day cell]=7,[current day total only],[current day total + prior day accumulated total])

    Hope that makes sense
     
  7. Dolph

    Man of Honour

    Joined: 17 Oct 2002

    Posts: 48,408

    Location: Plymouth

    If you want to do that fully automatically it could get a little complicated. The best way would be to create a custom function using the Datepart VBA function to identify the week number, and then add together the associated quantities.

    Alternatively, the if statement idea will work, but getting it will involve a fair bit of thought to get it to work without having to write the if statement manually each time.
     
  8. moid

    Gangster

    Joined: 27 Jun 2004

    Posts: 459

    Location: Darlington - UK

    Why would you need to write it each time? Just copy the formula down everytime new rows are added. VBA is overkill for this task
     
  9. Dolph

    Man of Honour

    Joined: 17 Oct 2002

    Posts: 48,408

    Location: Plymouth

    Depends whether you want to waste space by having an unnecessary count column in the spreadsheet or not... Generally unnecessary repeating data taking up space is a bad idea. Using VBA you can generate the day on the fly and reset the count if it is on a monday which is much more efficient.

    I'm not sure if it's a day count column in the example or the actual data for the day. From the running total, it's the latter not the former, and as such you could not expect the '7' to be repeated every week.
     
  10. moid

    Gangster

    Joined: 27 Jun 2004

    Posts: 459

    Location: Darlington - UK

    No need for the count column. Simply use the weekday() function which will return the weekday number (use ,2 at the end for a Mon to Sun week). Build this into the IF statement and hey presto.