Formula based on cell positions from target cell

Question

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}

Answer 1

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.

Answer 2

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.