Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

For example, if column C holds the value of a transaction and column D holds the balance, then the formula in row D3 might be =D2+C3. This formula would be repeated for every row in the ledger.

A common annoyance when maintaining this type of spreadsheet is that if you insert a row into the middle of such a ledger, that row may be skipped when calculating the balance-- the formulas in the subsequent row have to be updated along with the formulas in the inserted row.

Do you have any techniques for avoiding these problems?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

Excel OFFSET Formula Example

share|improve this answer
    
That's excellent. Then the only piece left is whether there is a way to get Excel to auto-enter a formula like this when adding new rows at the bottom of the spreadsheet. I swear I've seen sheets that do this, but it isn't something I can reproduce reliably. –  davidcl Dec 14 '09 at 22:40
    
I'm going to go ahead and accept this answer-- I'll edit my question so it omits the other part. I want to do some experimentation and maybe I'll ask the other part again when I'm clearer about the behavior I'm looking for. –  davidcl Dec 16 '09 at 0:04
1  
@davidci: The formula =C3+OFFSET(C3,-1,1) is correct if column C contains the value of the transaction and column D contains the balance. I've attached a screenshot example showing the formulas for reference. Let me know if you have any questions. –  Matthew Rankin Jan 6 '10 at 15:14
    
Gah, you're right, I'm not sure how I managed to get myself so confused. I was using =C3+OFFSET(D3,-1,0), which is equivalent. (I'm deleting my previous comment with the wrong formula to make this less confusing for future readers) –  davidcl Jan 8 '10 at 18:15
1  
I'm glad that I could help. I'm also grateful for the StackExchange websites, like SuperUser.com, StackOverflow.com, etc., so that we can easily share this information. I've added Excel as one of my interesting tags, so I'll be on the watch for future questions. –  Matthew Rankin Jan 21 '10 at 15:25
show 1 more comment

You have multiple options one might be that one excel field holds the sum of a complete row. If you now add a row, it doesn't matter.

The other solution I use is that the sum get row number 1 and there I write sum(A1:A1000), well knowing that my current highest number is 150. This way I can add rows and they still count.

Solution number 3 is to check which operations in which program will make the spreadsheet change the sums automatically. In numbers for example the field A16 contains: sum(A1:15) and this is changed automatically to A17 containing sum(A1:16), if I add a line before the line 15, not the line 16! So I always keep one line at the end empty and add new lines before that! I think, this also works in excel and openoffice...

share|improve this answer
1  
I'm not sure any of these really get at my actual question. The formulas in my spreadsheet aren't sums of ranges; each row depends only on the previous row. –  davidcl Dec 14 '09 at 19:45
    
OK, this is different then. If the formula that you are using is always the same, then this might help you: Select the first row and copy it as many times down as you need id. E.g. I have a list of something in one sheet and many math operations on these things on another sheet. Now I add a line in the first sheet and nothing changes in the second. So I copy paste the first line of the second sheet down (+1 rows now) and again have a complete reference to sheet 1. –  Thomas Dec 14 '09 at 20:00
add comment

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)
share|improve this answer
    
That's only an issue if the column you add falls between the formula and the columns it references. I can see how this kind of formula could be used for the type of ledger I was asking about, but the offset solution seems more intuitive and less fragile to me. Thanks! –  davidcl Apr 15 '10 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.