4

I have a spreadsheet that I use to keep track of my paid time off (PTO). I accrue a certain number of hours each pay period (which I know in advance). When I take some time off, I add a row and keep a running total.

The data typically looks something like this:

     A       B        C         D
  +--------------------------------------
 1|Date     Accr     Used     Balance
 2|start                        20
 3|Jan-7      4                 24
 4|Jan-12              8        16
 5|Jan-21     4                 20
 6|Jan-28              4        16
 7|Feb-4      4        8        12
 8|Feb-18     4                 16
 9|Mar-3      4                 20

The formula to calculate the balance is essentially: take the previous balance, add any accrued hours, and subtract any used hours. So the formula in D3 is =D2+B3-C3, in D4 it's =D3+B4-C4, etc. (I do it this way because sometimes I take time off on the same day that I accrue it. I only want one row per date with activity.)

When I go to add some time off, I generally insert a row for the date when I'm taking the time (for instance, if I'm taking time off on February 22, I'd insert a row between rows 8 and 9). Unfortunately, that causes the formula in row 10 (the former row 9) to change to =D8+B10-C10. This isn't the end of the world, because I can just auto-fill from the rows above (again). However, sometimes I'm adding a record using the Google Sheets Android app, and it's not easy to do auto-fill on that.

I was wondering if I could tweak the formula so that insertion of a row wouldn't cause it to adjust like that.

I do know that I can use $ to prevent references from changing, but I'd need to do that for every cell, and that's even more work.

Can I create a formula that does the following?

{cell above} + {cell two to the left} - {cell one to the left}
3

The command offset does what you want: =offset(D3, -1, -2) is the value 1 row above and two columns to the left of D3. So, your formula for D3 could be

 =offset(D3,-1,0) + offset(D3,0,-2) - offset(D3,0,-1)

Extended down the column, it will do the intended computations regardless of row insertions.

1

An alternative to the use of OFFSET is the use INDIRECT and the R1C1 notation.

Note: For relative positioning use R[1]C[1].

The formula from D3 to D9 could be:

=INDIRECT("R[-1]C[0]",FALSE)+INDIRECT("R[0]C[-2]",FALSE)-INDIRECT("R[0]C[-1]",FALSE)

Remarks: For better or worse, the references will not change if new rows or columns are added.

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.