Trouble with Excel Formula?

[Beev]

Hey folks.

First of all, hello, as im a new member. Im not a big user of excel, but I use it occasionally for basic calculations. I dont use it at work or anything like that!

Ive been workin on a budget manager in Excel. Heres a rough guide to how it works.

To explain things with cell references, here goes (sorry if this get really confusing!).

Cell A - My Balance from that month (if i get paid £900, thats the value entered into this cell!)
Cell B - Represents an overdraft for £500
Cell C - Takes The the value from Cell A, and subtracts the value from Cell B (900-500 = 400, for example)
Cell D - Is a value representing my outgoings every month (Rent, Phone Bill etc.)
Cell E - Subtracts Cell C from Cell D, giving a Remainder after monthly outgoings
Cell F - Is a value that represents all the withdrawals I make from the bank account. For example, if I take out £30, I set the value as £30, then take another £20 out, I change that value to £50.
Cell G - Takes the value from Cell E and subtracts the value from Cell F, giving a grand total of exactly how much money I have left in my bank account!

Now, that bit was easy, now for the hard part!

My bank charges 15% interest on my overdraft, after my overdraft goes over £250. So how can I calculate this? It would have to be Cell G, currently I have a separate cell, that just takes the total from Cell G, and divides it by 1.15, only problem is, that multiplies any value by 15%. I tried to set an IF statement that states that if the value is equal to or more than -£250, it starts to add 15% onto (multiply by 1.15) the total! So for example, if the total is -£250, and I add a further £10 to Cell F, it actually adds a further £11.50 to the value of this cell, setting the value to -£261.50! All I get is an error message in the Cell saying #VALUE.

The actual code for my formula is =B19+IF(-250,"*1.15"), whether that is correct, or if it is way off I dont know! Bear in mind that B19 represents Cell G, but id like to incorporate this formula into Cell G, as opposed to being a different cell altogether.

I can provide the spreadsheet if you folks need it!

Yours,
Beev

[NBVC]

Assuming your not charged interest on the first £250 and only on the balance, then try:

=B19+(MAX(0,B19-250)*1.15)

[Beev]

Thanks for the info.

That didnt work, Now it just doesnt add 15% to the total.

Yeah my account reaches £250 interest free, once I go over £250, say £251, then I start to get charged 15% interest.

Yours,
Beev

[NBVC]

Thanks for the info.

That didnt work, Now it just doesnt add 15% to the total.

Yeah my account reaches £250 interest free, once I go over £250, say £251, then I start to get charged 15% interest.

Yours,
Beev

That formula takes what is in B19 and add 15% to the portion greater than 250.

So if I enter 250 in B19, the formula returns 250. If I enter 251, the formula returns 252.15.

Not right?

If not, you might need to elaborate and/or attach a zipped sheet.

[daddylonglegs]

So if B19 is -240 you want it to stay that way but if it's -300 you want 15% deducted from the entire negative balance, i.e. you get -345?

Try

=IF(B19<-250,1.15,1)*B19

Assuming you want this in G2 and G2 is currently

=E2-F2 then replace with the formula

=IF(E2+250<F2,1.15,1)*(E2-F2)