[Solved] Running total column

Discuss the spreadsheet application

[Solved] Running total column

Postby jeffnc » Mon Jul 21, 2014 6:05 pm

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?
Last edited by Hagar Delest on Mon Jul 21, 2014 9:23 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.0.1 on Windows Vista Home Premium, SP2
jeffnc
 
Posts: 4
Joined: Sun Jul 06, 2014 5:43 pm

Re: Running total column

Postby crusader » Mon Jul 21, 2014 7:28 pm

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)
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 494
Joined: Sun Jan 20, 2008 5:06 am

Re: Running total column

Postby jeffnc » Mon Jul 21, 2014 7:30 pm

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
OpenOffice 4.0.1 on Windows Vista Home Premium, SP2
jeffnc
 
Posts: 4
Joined: Sun Jul 06, 2014 5:43 pm

Re: Running total column

Postby RoryOF » Mon Jul 21, 2014 7:36 pm

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)
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31534
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Running total column

Postby crusader » Mon Jul 21, 2014 7:41 pm

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...
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 494
Joined: Sun Jan 20, 2008 5:06 am

Re: Running total column

Postby jeffnc » Mon Jul 21, 2014 7:48 pm

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?
OpenOffice 4.0.1 on Windows Vista Home Premium, SP2
jeffnc
 
Posts: 4
Joined: Sun Jul 06, 2014 5:43 pm

Re: Running total column

Postby RoryOF » Mon Jul 21, 2014 7:53 pm

Pull it down for as many rows as you feel are necessary. I used 400 and after 2.25 years am at 260 rows.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31534
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Running total column

Postby crusader » Mon Jul 21, 2014 7:55 pm

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... :D
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 494
Joined: Sun Jan 20, 2008 5:06 am

Re: Running total column

Postby jeffnc » Mon Jul 21, 2014 7:56 pm

OK I see what you mean crusader. That seems to work.
OpenOffice 4.0.1 on Windows Vista Home Premium, SP2
jeffnc
 
Posts: 4
Joined: Sun Jul 06, 2014 5:43 pm

Re: Running total column

Postby crusader » Mon Jul 21, 2014 8:00 pm

jeffnc wrote: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.
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 494
Joined: Sun Jan 20, 2008 5:06 am

Re: [Solved] Running total column

Postby nevergetany » Sun Jan 31, 2016 3:29 am

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
spreadsheet headers.jpg (3.96 KiB) Viewed 6286 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.

spreadsheet for illustration.jpg



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.
Attachments
spreadsheet headers.tiff
spreadsheet headers.tiff (2.91 KiB) Viewed 6286 times
OpenOffice 4.1. windows 10 Pro
nevergetany
 
Posts: 4
Joined: Mon Jul 29, 2013 4:12 am
Location: Oregon USA

Re: [Solved] Running total column

Postby crusader » Sun Jan 31, 2016 5:38 am

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.
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 494
Joined: Sun Jan 20, 2008 5:06 am

Re: [Solved] Running total column

Postby nevergetany » Sun Jan 31, 2016 6:31 am

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?
OpenOffice 4.1. windows 10 Pro
nevergetany
 
Posts: 4
Joined: Mon Jul 29, 2013 4:12 am
Location: Oregon USA

Re: [Solved] Running total column

Postby thinman3 » Sun Jan 31, 2016 6:35 am

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


Running total in column.ods
(7.45 KiB) Downloaded 422 times
3.4.1 & 4.1.5 on MS Windows 7 Pro x64
thinman3
Volunteer
 
Posts: 382
Joined: Sat Jul 11, 2009 8:53 pm

Re: [Solved] Running total column

Postby eeoulo » Mon Jan 06, 2020 9:46 pm

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!
OpenOffice 4.1.7
WIN 10 64pro
eeoulo
 
Posts: 1
Joined: Mon Jan 06, 2020 9:38 pm


Return to Calc

Who is online

Users browsing this forum: fiona87, Google [Bot] and 20 guests