Sep 18 2021 12:35 AM
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
Sep 18 2021 12:43 AM
Sep 18 2021 12:52 AM - edited Sep 28 2021 05:42 PM
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
Sep 18 2021 03:23 AM
SolutionAnother 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)
Sep 29 2021 12:13 AM
Sep 30 2021 03:29 PM
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.
Oct 05 2021 08:16 PM
Sep 18 2021 03:23 AM
SolutionAnother 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)