adding percentages to IF statements

[sammy011]

Hello i would really appreciate your help of you could help me out with a formula.

Problem no.1= I dont understand how I can setup IF statement on spreadsheet for overdraft which starts on D29.

(question is)

If the company 's closing balance in any month is negative, authorized overdraft setup to £5k.

overdraft is 3% per month on the overdraft provided it does not go over £5k. charge is paid the next month (e.g company has a negative closing balance in april then the charge is paid in may). If the company overdrawn over £5k then the cost is 15% per month. Jan does not have any overdraft

[oeldere]

Not realy sure what your up to, but i give it a try.

With an if statement (see the green cell)

With VLookup in a table (see the orange cell).

[sammy011]

Thanks for helping me out, you have got further than Iv have. Iv been trying for literally 3hours!!

Basically January will have no overdraft to be paid. February will have an overdraft to pay because the net profit for January is -£36,075.55 so since the overdraft that is needed is more than £5000 the interest rate will be 15%. However this needs to be automated. If the company needs an overdraft in the future which is less than £5000 they will only get charged 3%.

In February im expected to take more than £5k because iv gone -£36,075.55

So you got further than I did but isnt there supposed to be a formula in overdraft d29?

[oeldere]

You didn't add the result, so i try again.

See the yellow cells.

[sammy011]

Hmm i think thats right. The vlookup has slight confused me However when i enter the closing balance and its a positive it brings up an error message.


I seriously appreciate the help.

[oeldere]

Please don't add an docx but an excel file.

Or are your working on a word doc.

In that case you are in the wrong forum.

[sammy011]

is that vlookup needed? Iv been told it isnt needed but not 100%

[oeldere]

Please Login or Register  to view this content.

I respond on this sentence.

If you need help with that, please add the file.

[sammy011]

That file only contains the question which i have re-written out.

[oeldere]

I am not interested in making a file for you.

I will help you if you get trouble with an excel formula.

In that case I need an EXCEL file and not an word doc.

[sammy011]

No that file just contained the question. Basically, it overdraft should be an IF statement not a vlookup. So i need a formula which will say if the company takes out a overdraft over £5k then they will be charged 15% intrest. If they take out a overdraft which is less than £5k they will be charged 3% only in the following month.

this is seriously killing my brain!

[oeldere]

Another question.

Is this a school assignment?

If so, please read the forum rules on that item.

[sammy011]

no its not an assignments a data entry for profit or loss for a business. set criteria i have to meet. i think somthing needs to be added to the data entry area at the top, however not sure. Question i have written out in depth.

[oeldere]

In that case i don't understand why it is not allowed to use VLookup (to solve your problem).

Maybe you can explain it to me.

[sammy011]

because a data entry has been entrered at top. If a Vlookup is in place then it is looking for the data from the vlookup located if you understand what im trying to say. Wouldnt it been simple if a a formula could do the calculations without the vlookup? The vlookup has sort of confused me.

[oeldere]

VLookup is much easier to update (comparing multiple IF statements).

Use F1 (help) and try to understand the formula (it's a realy basic one), so i advice you to learn this one, before using complex formula.

[sammy011]

Ok i will give it a go tomrrow. It is quite late now, plus its christmas so dont want to stress to much!!

thanks again for your help. I seriously appreciate your time and effort. I cant thank you enough you are doing an amazing job!

Merry christmas!

[oeldere]

Same to you

Glad i could help (to put you in the right direction).

[Steve N.]

Folks, if I may...

Sammy, both the IF statement you are asking for and theVLOOKUP Oeldere has provided will give you the same result. However, the benefit of using a VLOOKUP is that if the overdraft threshold or percentage rate were to change, you would not need to open any formulas to update them. All you need do is change the appropriate field in the Rate Table. The rate table will work on any sheet in the workbook.

See the attached sample. I used oeldere's fine work to illustrate (Oeldere - hope you don't mind).

I converted the rate info to a table named it Overdraft_Rate. That is now in the VLOOKUP formula. I also added the IFERROR wrapper to the VLOOKUP to prevent it from returning #N/A, which would prevent the s/s from making some calculations.

Hope this helps...

[oeldere]

@Steve N.

Of course, I don't mind.

You have done a nice job, adding a table with defined names.