[Solved] Running total column

[jeffnc]

In column E, I have a list of gains and losses. In column F, I want to keep a running total of those. My current formula is =SUM($E$2:E144). However I have to keep updating that. Every time I add a new entry in column E, I have to update the formula in the cell in column F. For example after my next entry, I will update it to =SUM($E$2:E145).

Actually I suppose it would be more efficient to write that as = E144 + F143, = E145 + F144, etc., so it doesn't calculate the whole column each time.

But anyway.... this is tedious - is there a permanent formula that I won't have to update every time?

[crusader]

I am assuming the formula is sitting at the top (or near top) of column F and not scrolled down column F.
=SUM(E2:E99145)

[jeffnc]

No, this is a running total that appears in each cell of column F.

Example
E F
1 1
3 4
2 6
7 13
1 14
5 19

[RoryOF]

I use d1+c2-b2 in D2 and pull down the black square in the corner of D2. Column C is additions, column B is deductions. column D gives the running total. D1 is the opening figure (possibly 0)

[crusader]

Copy your formula all the way down column F. This will put the current answer all the way down to where you copy the formula -- but change when the appropriate cell in column E is filled. Alternatively, in cell F145, enter:
=IF(E145="";"";SUM($E$2:E145))
and copy the formula as far down on column F as you need.

Hope this helps...

[jeffnc]

Rory, that certainly is easier. I didn't even really notice that little black box.

But as far as anyone knows, there isn't an alternative to this manual method? No formula to set a column to a running total?

[RoryOF]

Pull it down for as many rows as you feel are necessary. I used 400 and after 2.25 years am at 260 rows.

[crusader]

You don't have to copy down the formula in column F each time you add a value to column E. Copy the formula down column F as far down as you need (e.g. row 100000) only once - that's it!

Then sit back, relax, and marvel at the product that AOO is and share it with others...

[jeffnc]

OK I see what you mean crusader. That seems to work.

[crusader]

OK I see what you mean crusader. That seems to work.

Glad to "hear" that...

Please edit your first post (on this thread) and mark it solved using the green check mark.

[nevergetany]

For my application what I saw for running total didn't work. It is probably because I didn't know how to do it correctly. I need explanation in detail step by step and why we are doing it that way. Seeing it on video helps as long as there is audio explaining what and why of what is happening. But I don't think you can put a video on here, so maybe you have one on you tube that you can provide a link to. I am going to attempt to tell you what I want to accomplish. I downloaded a template that comes close to doing it, but it is copyrighted, and he did not give the formula. I am creating an INVOICE/BILLING STATEMENT. The Math Columns are:

spreadsheet headers.jpg (3.96 KiB) Viewed 9905 times

D {CHARGES} E {PAYMENTS} F {REMAINING BALANCE}

What I want is for Column F (on the row we are making the entries on) to automatically calculate and put in the total every time an entry is made in Column D and/or Column E. To illustrate:

Let's say the last entry shown in column F is $500.00 (the screen shows this as row 57); so on row 58 column E you make an entry of $300.00; Column F (row 58) instantly shows $200.00. I don't even have to hit tab bar to get there, it has already read what I have entered, and puts the answer in. When I come back to this client at a later date I am ready to start making entries on row 59. I put and entry of $200.00 in Column D, and instantly Column F (row 59) shows $400.00. And that is ok, when I come back and start making on entries on row 60, it will be able to put it's figure in column f row 60; HOWEVER, right now we are still on row 59 and in Column E I make an entry of $100.00 and Column F (still row 59) now changes automatically to $300.00. So now when we come back later to make entries on row 60 (and beyond), column F will always be up to date. The basic formula is column F EQUALS(=) last entry shown in column F PLUS(+) entry made in column D MINUS(-) last entry in column E. Unfortunately, I don't know how to write the formula for OpenOffice Calc. Nor where to put it.

This is how the previous paragraph looks like on Screen: {SEE THE ATTACHMENT BELOW AS IT WOULD NOT COME OUT RIGHT IN THIS SPACE}....You may have to enlarge by double clicking on it to see it better.

I don't know if this can be done or not, but it would be nice if entries in Column D would automatically come out in BLUE when being entered. And the entries in column E automatically be in GREEN; and the entries in Column F automatically be in Brown UNLESS it is a negative figure, in which case it is RED. I haven't seen this done yet, but it would make a good you tube instructional video if it can be. I like to see these things automated as much as possible, so I don't have to keep re-inventing the wheel.

[crusader]

It will be easier - and faster - to get assistance if you can upload your actual spreadsheet (as opposed to just headers). Please be sure to remove sensitive data before uploading.

[nevergetany]

I'm still in the process of creating it. All I have so far is the business heading, logo, and the column headings. There is no data in it. I have no problem uploading what I do have. When I upload it do you want me to leave all my Business info in, or do you want the business info removed, and just let the column headers and lines be shown?

[thinman3]

Based on what you described, see if the attached example does what you want.....

Running total in column.ods

(7.45 KiB) Downloaded 675 times

[eeoulo]

Thanks to all who helped in this thread, especially thinman3 for the handholding by sharing the ods. That's exactly what I needed!

My question is what if I want to go one further and have 2 running balances, say for 2 cards. So now I've got G,H, and I functioning the same as D,E,F in your example. How do I get F and I to carry forward the balance when there isn't a transaction on that row? So for example: I'm using the same setup that thinman posted but I have a 2nd card with + in G, - in H, and the total in I Now in row 64 and 65 I'm going to do transactions in G and H, how do I get column F to still carry it's balance down so that it's ready for a related transaction in row 66? Then when I do the d/e/f related entry in 66 how do I keep the balance rolling in I even though there isn't a relevant transaction that row?

Thanks in advance!