Naive spreadsheet question - how to produce a running total?
I'm a total newcomer to spreadsheets but I think/hope a spreadsheet is what I need to fulfil a very simple requirement. I want to maintain a simple balance of income and outgoings, e.g.:- Date Description Credit Debit Balance 1/1/2012 Monthly charge £200.00 -£200.00 5/1/2012 Received cheque £200.00 £0.0 21/1/2010 Miscellanous expense £5.00 -£5.00 .... etc. I can manage the various data entry columns in Gnumeric but what formula do I need in the Balance column? Firstly I can't find the syntax for "all cells to the end", all the Help seems to tell me is how to enter a range, i.e. C2:C12 means the range from C2 to C12, but how do you say "to the end"? Secondly how do you get a running balance without having to re-enter the formula in the Balance column for every row you enter? -- Chris Green
On Sun, 15 Jan 2012 16:11:58 +0000, cl@isbd.net said:
Firstly I can't find the syntax for "all cells to the end", all the Help seems to tell me is how to enter a range, i.e. C2:C12 means the range from C2 to C12, but how do you say "to the end"?
Well, you could just use a sufficiently large row number for the second cell in the range. However, what I do is below...
Secondly how do you get a running balance without having to re-enter the formula in the Balance column for every row you enter?
Assume row 1 is the heading, and the five columns in your example are columns A-E. The first balance, then, is in cell E2. The formula should be =sum(c$2:c2)-sum(d$2:d2) When you copy that down (using the small square that appears in the bottom RH corner of the cell when the mouse is in that area: click and drag), the formula will repeat with the '$2' part not changing. For example, in the next cell down, E3, the formula will automatically be =sum(c$2:c3)-sum(d$2:d3) In other words, the balance cell formula will automatically be the sum of column C from row 2 to the current row minus the sum of column D from row 2 to the current row. Does that answer your question? -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
On Sun, Jan 15, 2012 at 04:32:39PM +0000, Keith Edmunds wrote:
On Sun, 15 Jan 2012 16:11:58 +0000, cl@isbd.net said:
Firstly I can't find the syntax for "all cells to the end", all the Help seems to tell me is how to enter a range, i.e. C2:C12 means the range from C2 to C12, but how do you say "to the end"?
Well, you could just use a sufficiently large row number for the second cell in the range. However, what I do is below...
So there isn't actually a syntax for "all the way to the end"?
Secondly how do you get a running balance without having to re-enter the formula in the Balance column for every row you enter?
Assume row 1 is the heading, and the five columns in your example are columns A-E.
The first balance, then, is in cell E2. The formula should be
=sum(c$2:c2)-sum(d$2:d2)
When you copy that down (using the small square that appears in the bottom RH corner of the cell when the mouse is in that area: click and drag), the formula will repeat with the '$2' part not changing. For example, in the next cell down, E3, the formula will automatically be
=sum(c$2:c3)-sum(d$2:d3)
In other words, the balance cell formula will automatically be the sum of column C from row 2 to the current row minus the sum of column D from row 2 to the current row.
I'd read through the help on relative/absolute column references and saw that it could do some of what I wanted but was hoping for something more straightforward.
Does that answer your question?
Well, maybe, but it still seems to me that I have to repeat the above "copy that down" of the balance column every time I enter a new row. That seems like something the computer ought to be able to do for me. Isn't there some way of 'auto filling' a column with the same calculation as the previous row whenever a new row is entered into the spreadsheet? If this was a more realistic case where one wanted running balances of "Total including VAT", "Total excluding VAT" and "VAT" it would get very tedious having to make sure the formulae were copied down as one entered new values. -- Chris Green
On Sun, 15 Jan 2012 16:44:48 +0000, cl@isbd.net said:
So there isn't actually a syntax for "all the way to the end"?
I don't know.
I'd read through the help on relative/absolute column references and saw that it could do some of what I wanted but was hoping for something more straightforward.
Such as?
Isn't there some way of 'auto filling' a column with the same calculation as the previous row whenever a new row is entered into the spreadsheet?
No need. Copy the formula/formulae all the way to the foot of the spreadsheet in one go. That will put the last value of the balance in all the unused rows, but you could easily fix that with an "if" formula. In English: if cell B this row is empty, insert "" else insert the formula I gave before.
If this was a more realistic case where one wanted running balances of "Total including VAT", "Total excluding VAT" and "VAT" it would get very tedious having to make sure the formulae were copied down as one entered new values.
As I say, copy them to the end of the spreadsheet once. Job done. -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
On Sun, Jan 15, 2012 at 05:13:27PM +0000, Keith Edmunds wrote:
On Sun, 15 Jan 2012 16:44:48 +0000, cl@isbd.net said:
So there isn't actually a syntax for "all the way to the end"?
I don't know.
I'd read through the help on relative/absolute column references and saw that it could do some of what I wanted but was hoping for something more straightforward.
Such as?
A way to put a formula in the column as a whole in the same way as right clicking on the column header allows one to set the font size, style, justification, etc. It just seems such an obvious sort of thing to want to do.
Isn't there some way of 'auto filling' a column with the same calculation as the previous row whenever a new row is entered into the spreadsheet?
No need. Copy the formula/formulae all the way to the foot of the spreadsheet in one go. That will put the last value of the balance in all the unused rows, but you could easily fix that with an "if" formula. In English: if cell B this row is empty, insert "" else insert the formula I gave before.
If this was a more realistic case where one wanted running balances of "Total including VAT", "Total excluding VAT" and "VAT" it would get very tedious having to make sure the formulae were copied down as one entered new values.
As I say, copy them to the end of the spreadsheet once. Job done.
Hmm, OK, but it's not exactly 'pretty' is it! When I look at my spreadsheet I don't want to see 50 rows where only the first ten have values in them, I just want to see the rows with values. Where's the 'end' anyway, it could have hundreds or thousands of rows after a few years use with only the last few tens of rows being shown. Sorry, I was just hoping a spreadsheet would be a cleaner/tidier solution to my requirement than it actually is. -- Chris Green
You're quite right, they are really quite messy because of exactly what you have noticed, being cell based. This is also what makes them so prone to error, but also so easy to use for people who don't realise they are actually writing programmes. The real answer if determined to use a spreadsheet is probably to use macros in OO, or VB in Excel. But the last time I tried to do anything serious in a spreadsheet, I did what the other writer said, just pasted the formula into the whole column. The other problem with them is they are so slow. The last time I did this, I found it necessary to do all kinds of lookups and use a matrix. Well, it started to take minutes to load the thing. Not to get the report, to load the thing in the first place, because of all the automatic calculation that was happening as it loaded. Yes, you can turn that off. But its a total kludge. Same thing done in high level scripting language, you just put the data into variables in memory, then do 'for each x...' and it takes a couple of seconds.
Sorry, I was just hoping a spreadsheet would be a cleaner/tidier solution to my requirement than it actually is.
On Mon, Jan 16, 2012 at 12:52:53PM +0000, Peter Alcibiades wrote:
You're quite right, they are really quite messy because of exactly what you have noticed, being cell based. This is also what makes them so prone to error, but also so easy to use for people who don't realise they are actually writing programmes.
The real answer if determined to use a spreadsheet is probably to use macros in OO, or VB in Excel. But the last time I tried to do anything serious in a spreadsheet, I did what the other writer said, just pasted the formula into the whole column.
The other problem with them is they are so slow. The last time I did this, I found it necessary to do all kinds of lookups and use a matrix. Well, it started to take minutes to load the thing. Not to get the report, to load the thing in the first place, because of all the automatic calculation that was happening as it loaded.
Yes, you can turn that off. But its a total kludge.
Same thing done in high level scripting language, you just put the data into variables in memory, then do 'for each x...' and it takes a couple of seconds.
That's the conclusion I have come to, I'm aiming to write something in Python (simply because it's what I find most comfortable) with a database providing the long term storage. At the same time as doing the sums I'm going to get the Python to help me get the other thing I've been hankering after - a simple way to edit database tables in a multi-row mode.
Sorry, I was just hoping a spreadsheet would be a cleaner/tidier solution to my requirement than it actually is.
_______________________________________________ main@lists.alug.org.uk http://www.alug.org.uk/ http://lists.alug.org.uk/mailman/listinfo/main Unsubscribe? See message headers or the web site above!
-- Chris Green
Firstly I can't find the syntax for "all cells to the end", all the Help seems to tell me is how to enter a range, i.e. C2:C12 means the range from C2 to C12, but how do you say "to the end"?
In Excel, at least, =SUM(C:C) will sum the contents of numeric values in the whole of column C. Obviously you need to be careful to avoid circular references (i.e. don't put that in column C!), but that should help, Greg
participants (4)
-
Chris Green -
Greg Thomas -
Keith Edmunds -
Peter Alcibiades