Appleworks spreadsheet help

I keep my bank account in an Apple Works spreadsheet and it's quite easy.

I use six columns as follows:
A = Date of transaction
B = Payee / cheque number
C = Credits
D = Debits
E = Checked against statement (I fill this with a fill pattern when checking my statements)
F = Balance

I have a title in Row 1 and Column headers in Row 2, so we'll assume for now that you use the same style. You can then show your opening balance in Row 3. So in cell F3 you would enter your opening balance of £1100. Immediately below this in cell F4 you need to enter the following formula:
=SUM(F3-D4+C4)

This will take the value in F3 (your opening balance) then subtract any Debits in Row 4, then add any Credits in Row 4. Normally you'd only have either a Debit or a Credit in a single row, but this formula allows for one or the other, or even both.

Now you need to populate the rest of your Balance column with the same formula. Click and hold on cell F4 then drag down to the bottom of your spreadsheet - it can take a while to reach the bottom. When all the relevant cells are selected press Apple+D to Fill Down. What you'll see immediately is that all the cells now have your opening balance of £1100. Click on any of the cells though and you'll see in the cell detail window (just below the header bar) that the formula has been copied but the values have been changed to match the row you are in. Click on any cell below F3 and you'll see that the formula corresponds to that cell e.g. F17 contains the formula =SUM(F16-D17+C17)

Now try entering figures in the Debit and Credit columns and you should immediately see the balance column changing.

I think that's clear enough, but if you have any more questions feel free to ask.

 

I'm just about to leave work for the day, but I'll experiment a bit when I get home. In the meantime have you tried =SUM(1100+E3) in column F. That way if your running balance (by which I think you mean your actual current account balance) is in credit by, say £500, your available balance in column F will show as 1100+500 = £1600. If your running balance is -£50 column F will show as 1100+(-50) = £1050. Adding a negative number will subtract that number.

I'll check back when I get home.

 

Oh good - glad it worked. One thing to remember is that the formula is expecting numbers in columns C and D, so if you enter anything else, even a space, it will mess up columns E and F. If you get #VALUE! in either of the columns it means you've got a non-numeric character in column C or D in the row where #VALUE! starts. Just click on the C and/or D cells, press Delete and enter.

I've said it before, and I'll say it again ... Apple Works is greatly underrated!