# Formula based on cell positions from target cell

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}
``````

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.

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

Note: For relative positioning use RC.

The formula from D3 to D9 could be:

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

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