5

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?

4 Answers 4

5

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

6
  • 1
    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, 2009 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, 2009 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. Jan 6, 2010 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, 2010 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. Jan 21, 2010 at 15:25
0

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...

2
  • 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, 2009 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, 2009 at 20:00
0

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)
1
  • 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, 2010 at 12:44
0

When I add new rows, i select the entire row (clicking on the actual row number to the left of Column A) then use CTRL-D to "duplicate" the row above it. Then I change the numbers and text that was not forumla-based. But you're right, this doesn't help when adding rows in-between if you are usuing a sort of "running total" or "cumulative" value as you describe. I liked Toc's response which uses SUM(C$2:C3) because it will be immune to this problem..

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .