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.