# Excel - running total per week

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

1. PaulStat

# 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

# Location: Newcastle/Aberdeen

Click on cell where that column finishes and click autosum?

3. PaulStat

# 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

# 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

# Location: Cotham, Bristol

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

6. moid

# 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

# 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

# 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

# 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

# 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.