Formula - Always reference cell above

Generally,

If you need you formulas to survive inserting, and reordering, then you need to be "sort-safe".

you can try this to see if it works for you:

E10=OFFSET(E10,−1, 0)+OFFSET(E10,0, −2)−OFFSET(E10,0, −1)

this is an issue in all spreadsheet programs. You will have the same issue when you go to delete a row in the middle somewhere, it will break all formulas below that refer to the row above.

There is no quick and easy fix in a spreadsheet for this. they did make sure that if you sort your data, the formulas will stay in place, referencing the cells they should. (this was actually fixed in an earlier version)

we can take advantage of that to fix your issue.

I normally have a column that i can sort by, like date or time. If you dont just make a new column that counts by ten.

Now enter the new row at the bottom of the data set with the new date, time por a number that falls between the two rows you want it to go.

now sort to put the row in the proper place. You should see your formulas fall in line nicely.

Follow the reverse to delete a row. Make its sort column value really large, sort, then delete the last row.

Jason

I have E2+C3-D3. I have copied that down throughout the entire E column

It sounds as if you want a running balance in column E, something like this:

Rather than using OFFSET–which is a "volatile" function that recalculates every time you make a change in your document, no matter where or how small, and thus can lead to sluggish performance–another approach is to use simple SUM functions containing an "expanding range" (the top cell of the range is anchored by a $ whereas the bottom cell is not anchored, so when you fill the formula down the range expands). It would look like this, and produces identical results, yet is not affected by addition or deletion of rows.

The formula in E2, filled down, is:

=SUM(C$1:C2)−SUM(D$1:D2)

SG

You pretty much said it in your question. You need OFFSET. Instead of

= E2+C3-D3

use

=OFFSET(E3,−1,0)+C3-D3

It didn't say you were adding new columns so I left C3 and D3 as they were. You can use OFFSET for them, too, if you need.