Excel: How to get a running total that resets every 7 days

Question

I need to create a formula that will find a running total for the week, then reset for the next week.

We have column D as the pay per hour, column E as the hours, column F as the pay for that day, and G as the total for the week so far

G is where we are having trouble

lets say for example we look at row 11:

D11 = 10
E11 = 2
F11 = 20
G11 = 20

So in row 17 it should be this:

D17 = 10
E17 = 2
F17 = 20
G17 = 140

Assuming D11-D17 is 10 and E11-E17 is 2

Starting at D18 then, the pattern should repeat so

D18 = 10
E18 = 3
F18 = 30
G18 = 30

So in row 24 it should be this:

D24 = 10
E24 = 3
F24 = 30
G24 = 210

Assuming D18-D24 is 10 and E18-E24 is 3

Any suggestions?

Thank you!

Answer 1

My idea for this (to be placed into row 2 and copied down):

=SUM(OFFSET($B$2,INT((ROW()-ROW($B$2))/7)*7,0):$B2)

Here $B$2 denotes that the value to be added is in column B, and the first such value is in row 2. It computes the number of rows between the current row and that starting cell and adds multiples of 7 to the starting row. INT() is responsible for the step function - it only increments if the difference reaches a multiple of 7.
To vary the starting cell, change 2 occurrences of $B$2 and the column in $B2.