1

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!

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.

  • Great! Please mark as answer then. – user1016274 May 14 '17 at 12:22
  • Marked! sorry for the late action – ChrisM May 15 '17 at 14:58
  • @user1016274,, nice & very useful solution,, +10 ☺ – Rajesh S Nov 30 '20 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.