Home » Questions » Computers [ Ask a new question ]

Excel annoyance: How to maintain formulas in a ledger?

Excel annoyance: How to maintain formulas in a ledger?

A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current row to a value from the previous row.

Asked by: Guest | Views: 38
Total answers/comments: 2
Guest [Entry]

"For the balance formula in cell D3 use =C3+OFFSET(C3,-1,1) based on your example of C3 having the amount and D2 having the previous balance. This way when you insert a row, you can just type in the new value and copy the balance formula from one of the other rows.

By using the OFFSET formula, you don't have to update each cell in the balance column."
Guest [Entry]

"The problem with the (good) solution which uses OFFSET is that if you add a column (not a row) then your formula will not work anymore. In plain words, in this case OFFSET protects you from the add of a row but not from an add of a column.
An alternative is to use the following formula in D3 which you can copy as many times as you want below:

=SUM(C$2:C3)"