Clueless about simple spreadsheet formula in Open Office

GunJack

you also need to format columns B and C to currency.....

GunJack

wealdroam wrote: »

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.

putting a blank in D6 if there are equal credits/debits will blank the balance for the future calculations!!! Don't do this!!!

wealdroam

GunJack wrote: »

putting a blank in D6 if there are equal credits/debits will blank the balance for the future calculations!!! Don't do this!!!

But it will only put a blank in column D if there is no debit or credit. This is only going to occur on rows beyond the last one with data. As soon as data, either a debit or credit line is added, that blank will be overwritten.

Kernel_Sanders

GunJack wrote: »

you also need to format columns B and C to currency.....

I've done that but have no idea why only some of the balances have been converted!
My mother has multiple savings accounts, mostly regular savers, and when she has a surplus of funds she transfers them to me to put in my accounts. I repay them when her accounts need funding again, but I'm paying her interest of 1.98% p.a. (monthly on the 14th). To calculate interest I only have to manually work out the number of days ending with the same balance and need a formula to multiply D by F to get the number of 'pound-days', the total of which would go in G14? It's then a simple matter of multiplying that by 0.0000541 which should give the interest due for the first month, which runs from 14th Aug to 13 Sep incl.
I've typed in the bare bones of these formulae but have had to omit the equals sign as it adds black cell numbers.
4

[Deleted User]

HI,

Kernel_Sanders wrote: »

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.

you can use the Decimal Place icon to add remove decimal points, click on cell or highlight column, then click on add/remove icon once or twice.

System

Hi

Dates can be used

Simply take the older date from the newer date, and add 1 if wanted.

This then displays the number of days, if you then divide this by 365 you get a fraction of a year.

01/01/15
15/01/15

is either 14 or 15 days.
14/365 is 0.0383561
15/365 is 0.0410958

So for £235.36 for 15 days it would be

£235.36 for 1 year @2% is 235.36 * 0.02 = £4.7072

Next divide 4.7072 by 365 and multiply by 15 days

or multiply by the figure obtained earlier 0.0410958

and get 19 pence.


All automated.

AND as stated earlier use an IF statement to only calculate the rows wanted.

Kernel_Sanders

wealdroam wrote: »

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

That produces an Err:508 which is 'missing bracket eg closing brackets but no opening brackets'. IDU that because I pasted in all 3 pairs of brackets.

Neil49

If all else fails then I suggest you download the free Money Plus Sunset Deluxe from Microsoft.

It does everything you want from above and more but once set up is easy to use and see at a glance details of all your accounts.

http://www.microsoft.com/en-GB/download/details.aspx?id=20738

It also appears to work on Windows 10

rmg1

wealdroam wrote: »

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.

From a personal point of view, I'd change the above formula to this:-
=if(and(b6="",c6=""),"",d5+b6-c6)

Put this formula into d6 and drag down as necessary.

Kernel_Sanders

The lull in responses yesterday made me do my own research and I've actually managed to devise a working spreadsheet. It only needs days with a common balance added together manually and it'll calculate a running total of the interest in column I. I'm still awaiting a solution to the bleaching problem, though (F-I are deliberately white).

johndough wrote: »

Dates can be used.......All automated.....
AND as stated earlier use an IF statement to only calculate the rows wanted.

I thought I'd try this, hoping that changing the date column from text to numeric might stop it calculating in advance but when I dragged the formula down, it allocated a row for every future date, overriding the dates I had previously input (see upper half):

rmg1 wrote: »

From a personal point of view, I'd change the above formula to this:-
=if(and(b6="",c6=""),"",d5+b6-c6)
Put this formula into d6 and drag down as necessary.

Just like Wealdroam's, that produces a 'missing bracket eg closing brackets but no opening brackets' error on the earlier (lower half) spreadsheet: