Excel - running total per week

Soldato
Joined
1 Mar 2003
Posts
5,508
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
 
Soldato
OP
Joined
1 Mar 2003
Posts
5,508
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
 
Man of Honour
Joined
17 Oct 2002
Posts
50,384
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.
 
Man of Honour
Joined
17 Oct 2002
Posts
50,384
Location
Plymouth
But that doesn't reset it for each consecutive week though? i.e. the running total resets every monday

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.
 
Associate
Joined
27 Jun 2004
Posts
456
Location
Darlington - UK
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.

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
 
Man of Honour
Joined
17 Oct 2002
Posts
50,384
Location
Plymouth
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

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.
 
Associate
Joined
27 Jun 2004
Posts
456
Location
Darlington - UK
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.

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.
 
Back
Top Bottom