Clueless about simple spreadsheet formula in Open Office

Kernel_Sanders

I just want to keep a record of deposits, withdrawals and a running balance of a bank account. I've now spent over an hour trying to find out how to devise a formula along the lines of D = (previous D) + B - C. If I try to insert such a formula in the toolbar shown, the = sign changes to the green tick! How do I tell it to only calculate from line 5?

lindabea

I would not have laid the spreadsheet the way you did it, as it's not well structured. However, you could make it work with the following formula
D6 = D5 + B6 - C6

Then repeat for the next line so that you will be bringing forward the previous D line.

Kernel_Sanders

How should it be structured?
Surely I don't have to monotonously go through dozens of lines advancing the figures one at a time?

lindabea

In openoffice, when you keyed in the formula in a cell, you can then scroll down and automatically copy that formula in all the cells in that column.

So in your case, put your opening balance in D5 place your cursor in D6 and key the formula =D5+B6-C6. Then copy the formula down column D.

debitcardmayhem

Put your opening balance in D5
Then in D6 do this =(click on D5) press +(click on C3) press -(click on C4) press enter
then click on D6 press Ctrl+C
then click on D7 press shift and down arrow to gighlight the 10 rows(say) then press Ctrl+v

Then you just repeat from Dx copy again CTRL+c) highlight mode in column D and paste again (ctrl+v)

lindabea

This is the way I would have laid the spreadsheet
A B C D E F
Date Payment Type Bal B/F Deposits Withdrawals Bal C/F

Then I would have copied cell F to cell C on the next line

Hope this makes sense

Kernel_Sanders

debitcardmayhem wrote: »

+(click on C3)

C3 doesn't appear to be interactive. Why would you add any amount from the withdrawals column?
This is just all too difficult, I think it'll be simpler for me to just write it out and and do the arithmetic by calculator..

GunJack

Kernel_Sanders wrote: »

C3 doesn't appear to be interactive. Why would you add any amount from the withdrawals column?
This is just all too difficult, I think it'll be simpler for me to just write it out and and do the arithmetic by calculator..

No, it's not, it's very simple and exactly the type of thing that spreadsheets were made for.... lindabea gave you the way to do this, once the formula is in cell D6, as has been said already, just then copy it down column D. All you need to do then is insert your date, deposit and withdrawal figures line by line and the spreadsheet will do the maths for you

debitcardmayhem

Kernel_Sanders wrote: »

C3 doesn't appear to be interactive. Why would you add any amount from the withdrawals column?
This is just all too difficult, I think it'll be simpler for me to just write it out and and do the arithmetic by calculator..

OK let me try again
Put opending balance in D5
In D6 type (ignore using click as I suggested I didn't say double click)
=D5+B6-C6

I was typing on a small screen with onscreen kb and got the columns mixed up:o

Then copy D6 to D7.........Dx using CTRL+C on D6 and CTRL+V on D7........Dx.

Kernel_Sanders

Does that mean I don't 'press shift and down arrow to highlight'? I didn't, but 3 problems became apparent when I put some figures in as a trial:
1. When I typed in 3300.00, it converted it to 3300 on calculation despite me setting the currency to show pence before I even started it.
2. It's bleaching the balance column.
3. It's calculating in advance all the cells to which I have copied the formula.

wealdroam

I would suggest you need something slightly more complicated in column D.

I'll borrow a line from debitcardmayhem's post and adjust it:
In D6 type
=IF(SUM(B6,C6)=0," ",SUM(D5,B6,-C6))

This is saying that if there are no credits or debits on this line then put a blank in D6, otherwise add together the credit and debit (usually only one or the other) to the previous balance and write that in D6.

Then to avoid having to copy and paste that all the way down the column, just click on the tiny little box at the bottom right of the D6 cell (can be seen on cell B11 on your screen print), and drag the formula down through all cells in that column.