# A Challenge - What IF, THEN calculation

#### graham holdsworth

##### New Member
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

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

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

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

"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...

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%

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

Replies
2
Views
252
Replies
5
Views
245
Replies
0
Views
218
Replies
17
Views
754
Replies
1
Views
709

Threads
1,203,605
Messages
6,056,258
Members
444,853
Latest member
sam69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back