Forum Discussion
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
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)
7 Replies
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
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)
