Appleworks spreadsheet help

[MacKat]

Hello everyone!

I am trying to use Appleworks spreadsheet to set up a running account.

Is there any way that I could implement the following

set up the available balance as 1100 and then subtract the amount of money going into another row of cells? Sorry - it is difficult to explain.

By starting off with one figure, can you command that rows change dependant on what values you put into other rows?

Alternatively, any good appleworks help sites out there?

Kat x

 

[MemphisSoulStew]

Hello everyone!

I am trying to use Appleworks spreadsheet to set up a running account.

Is there any way that I could implement the following

set up the available balance as 1100 and then subtract the amount of money going into another row of cells? Sorry - it is difficult to explain.

By starting off with one figure, can you command that rows change dependant on what values you put into other rows?

Alternatively, any good appleworks help sites out there?

Kat x

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.

 

[MacKat]

🙂 Thank you, thank you, thank you - you have been incredibly helpful 🙂

I have my spreadsheet set out in a similar way to yours in seems.

The only other problem I am having is within my final column.

I have an overdraft facility of £1100, and Column F is my available balance - next to column E which is my running balance.

What I would like in column F is the amount, including the overdraft, that I have available to spend. I have tried various formulas:

=SUM(1100-E3) seems to add the two together and make over 2000 (I wish) I am guessing thats because I have negative values in both columns. 😱

=SUM(-1100-E3) Gives me the correct result - although there is a negative sign before the number.

This is a basic maths problem I think... I'm not to good at maths! 😀

Thanks again 🙂

Kat x

 

[MemphisSoulStew]

🙂 Thank you, thank you, thank you - you have been incredibly helpful 🙂

I have my spreadsheet set out in a similar way to yours in seems.

The only other problem I am having is within my final column.

I have an overdraft facility of £1100, and Column F is my available balance - next to column E which is my running balance.

What I would like in column F is the amount, including the overdraft, that I have available to spend. I have tried various formulas:

=SUM(1100-E3) seems to add the two together and make over 2000 (I wish) I am guessing thats because I have negative values in both columns. 😱

=SUM(-1100-E3) Gives me the correct result - although there is a negative sign before the number.

This is a basic maths problem I think... I'm not to good at maths! 😀

Thanks again 🙂

Kat x

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.

 

[MacKat]

🙂

That worked for me!!!

Thank you again. You have saved me ages on the Mac Calculator working all that out 😛

Kat x

 

[MemphisSoulStew]

🙂

That worked for me!!!

Thank you again. You have saved me ages on the Mac Calculator working all that out 😛

Kat x

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!