Formula choosing next cells inconsistently

[Ned_O]

I am using a formula in a column that increases with each row.

=IF(C10>0,C10-C9,0)

It correctly becomes =IF(C11>0,C11-C10,0) in a row added at the bottom of the spreadsheet.

However, when I add a new row to the bottom, the formula in the row above the new one changes from a correct formula to an incorrect one. It becomes  

=IF(C10>0,C10-C8,0)   [the subtraction C10-C8 is 2 lines apart, instead of 1]

It was =IF(C10>0,C10-C9,0) [correct] before I added the new ottom row

[#2]

@Ned_O You would need a formula with OFFSET, like this:

=IF(C10>0,C10-OFFSET(C10,-1,0),0)

[#3]

Well. actually that seems to have stopped working. 

This was supposed to be a simple mileage tracker (for use on my cell when in the car). The idea was that I would only have to add the ending mileage (Column "C"), rather than start and stop, etc., and that mileage would be calculated in column "I".

It works except when I add more than 1 line. See highlighted cell "I51". It had the correct formula: =IF(C52>0, C52-OFFSET(C52,-1,0)0)

But when I added the next line (52) that ends up with the correct formula in I52, but I51 ends up converting to the same formula. I'm not sure why.

Any ideas?

Thanks, Ned

[#4 · Accepted solution]

@Ned_O 

Another variant you may add named formula (reference). If you stay, for example, on C10, define it as

Be sure to use absolute reference for column and relative one for row.

With that formula could be

=(C11-CellUp)*(C11 > 0)

[#5]

Would "Cell Up" change to the appropriate cell when a row is added? Ned

[#6]

@Ned_O 

If you stay on any cell (or enter formula into any cell), CellUp returns value from the cell above. Doesn't matter where current cell is and how you change it's location, by adding rows or by other way.

[#7]

Thanks, Sergei. After working through a few other issues like" inserting rows with the formulas in place" and updating the formulas in a column, I did get to work the "CELLUP" to work.
Thanks again. ned

[#8]

@Ned_O , great, glad to help