A Challenge - What IF, THEN calculation

[graham holdsworth]

Is there anybody out there that can help me (a basic Excel user), to develop a IF THEN formula based on the following please ?

PROBLEM - If someone earns more than £20 per hour they are earn 6% interest, if they earned between £20 and £12 per hour they earn 8% interest and if they earn between £10 and £12 they earn 10%.

How do I write 3 part formula in this in one cell, which is dependent on the hourly rate of an other cell (when input) ?

Many thanks

Mr G

 

[Richie(UK)]

Hi Graham,

Welcome to the board.

You could use an If statement but it would be more flexible in the long term (if you want to change rates or the number of bands etc) if you use a simple lookup formula.

How about something like this:

EDIT : (HTMLMaker code removed)


Have a table from A1 to B5, A1 heading "Pay" and B1 heading "Rate". A2 to A5 reads 0, 10, 12 , 20. B2 to B5 reads 0%, 10%, 8%, 6%.

In A9 enter the chosen rate of pay (eg £19). Then in B9 enter the following formula:
=VLOOKUP(A9,A2:B5,2,TRUE)

HTH

 

[macleanb]

Richie - are you seeing HTML OK - as all I am getting from these posts is gobbledygook

 

[masim]

for an If, then statement following works fine for me.

=IF(A1>20,B1*0.06,IF(AND(A1>=12,A1<=20)=TRUE,B1*0.08,IF(AND(A1>=10,A1<12)=TRUE,B1*0.1,0)))

where A1 houses your amounts earned and B1 houses your interest.

Regards
Asim

 

[Richie(UK)]

Richie - are you seeing HTML OK - as all I am getting from these posts is gobbledygook

Hi Ben,

Yeah, I got the gobbledygook too. I assume that its a (temporary?) casualty of the board upgrade.

EDIT : I must pay more attention to the Sticky posts! : )
http://www.mrexcel.com/board2/viewtopic.php?t=129208

 

[macleanb]

"Upgrade" - I always get nervous at work when that word is mentioned... Can you have a single word oxymoron - Only kidding smozgur et al - I upgrade stuff too and you do a great job on the board

EDIT: Must be nice to Admin & learn to spell handle correctly...

 

[Dutchy]

or this..

=IF(A1>20,B1*0.06,IF(A1>12,B1*0.08,IF(A1>10,B1*0.1,0)))

I'm assuming that £10 or less will earn 0%

 

[graham holdsworth]

Many thanks to you all for your help - to Richie(UK) , Asim and Duchy.

I will use ALL your approaches and try and solve it. However, I maybe back, but THANK YOU for the moment.

Regards,

Graham