 SOLVED

# Formula choosing next cells inconsistently

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

7 Replies

# Re: Formula choosing next cells inconsistently

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

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

# Re: Formula choosing next cells inconsistently

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

best response confirmed by Ned_O (Occasional Contributor)
Solution

# Re: Formula choosing next cells inconsistently

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

# Re: Formula choosing next cells inconsistently

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

# Re: Formula choosing next cells inconsistently

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.

# Re: Formula choosing next cells inconsistently

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

# Re: Formula choosing next cells inconsistently

@Ned_O , great, glad to help