Excel ledger balance/running total that survives copy insert row and sorting.
(Before we start there is also this non “volatile” solution =SUM(A$1:A10) used in column B as running total.)
The problem: cumulating column totals fail when rows are copy inserted or the data is sorted using data sort.
This SOLUTION was HUGE; for me. I just learned/discovered this. Using Excel OFFSET I can create a running balance/total column where when I INSERT any number of rows copied from elsewhere in a table, the running total remains correct instead of needing me to copy down the running total entry to cover the new row or rows.
Because the OFFSET syntax works using a logic that says “from this cell, go up one and use that cell as the one to add”. This means the reference to the cell one row above is fixed as always “the cell above this row” regardless of row insertions, it is neither ABSOLUTE nor RELATIVE referenced.
Insertion of a row does not change this reference because it is based on the cell containing the OFFSET formula minus one row, not a cell reference (such as A1) to the row above.
This means the reference to the cell one row above is not altered by an insertion (which usually it is). A small example is in the image below. To test it, copy insert any row and the running total will remain correct.
The trick is that the running total formula does not contain a reference to the cell above, so the insertion of a new (copied) row has no affect on the running total formula. This will save me thousands of cell copies.
To test this recreate the spreadsheet shown in this image below. Ignore column C and use its contents to create the formulae in Column B.
Notice the A2,3,4,5 references are to the same row, not any other row. This means row insertions cannot affect them. Likewise B2,3,4,5.
So the magic bit is that -1 (minus one). Because it refers to the cell above, without referencing the cell above (e.g. +B1) , it cannot be altered by row insertions from elsewhere in the table so the running total will always be correct.
Please let me know if I can make this explanation less tortuous.
