# Excel Formula to return rate in cell based on entry of a percentage.

#### Lmfacc

##### New Member
Hi there!
I am a bit of a novice so I apologize in advance for the terminology I use. I am hoping for some assistance in a calculator I have created and am currently enhancing for ease of use for the user.
Based on user entry of a percentage into one cell, I would like to populate a rate in another.
The percentages have various ranges.
For example:
If Cell A1 = 13% then I would like cell B1 to populate to 0.0187
The ranges are <=13.6%, between 13.7-14.8, between 14.9-15.8, between 15.9-16.8 and between 16.9-17., with each range populating a rate.
I have accomplished how to compare for up to 3 of ranges, but can’t figure out how to make it run for anything more.
Currently my formula for another scenario:
IF(A1<=7.1,0.0282,IF(A1<1.2,0.021,0.319))
Similar in premise if A1 is less than or equal to 7.1 return 0.0282, If A1 is less than 1.2 than return 0.021, anything in between return 0.319 This has functionality and works for my calculator.
But I don’t know how to apply something like this for a fuller range as specified above. Any insight would be so helpful.
thank you ever so kindly

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Dave Patton

##### Well-known Member
Edit the following for your rates.
N.B. C2 is self-contained; the array includes the information from the table of rates.

T202010b.xlsm
ABCDEF
1
20.170.05000.050.0%0.0187
313.6%0.02
414.8%0.03
515.8%0.04
616.8%0.05
717.1%0.06
8
1f
Cell Formulas
RangeFormula
B2B2=(A2>0)*LOOKUP(A2,E2:F7)
C2C2=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})

#### Lmfacc

##### New Member
Edit the following for your rates.
N.B. C2 is self-contained; the array includes the information from the table of rates.

T202010b.xlsm
ABCDEF
1
20.170.05000.050.0%0.0187
313.6%0.02
414.8%0.03
515.8%0.04
616.8%0.05
717.1%0.06
8
1f
Cell Formulas
RangeFormula
B2B2=(A2>0)*LOOKUP(A2,E2:F7)
C2C2=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})
Thank you Dave! This seems very reasonable! I am going to give it a try now! So very much appreciated

#### Dave Patton

##### Well-known Member
Check the ranges or results at the upper and lower limits of the ranges.
Check that amounts like 13.6 yield the correct result.

The ranges are not as they appear; I added .001. See the the details or cell contents in the initial post or the following.

T202010b.xlsm
ABCDEF
20.1480.02000.020.00%0.0187
313.61%0.02
414.81%0.03
515.81%0.04
616.81%0.05
717.10%0.06
8
1f
Cell Formulas
RangeFormula
B2B2=(A2>0)*LOOKUP(A2,E2:F7)
C2C2=(A2>0)*LOOKUP(A2,{0,0.0187;0.1361,0.02;0.1481,0.03;0.1581,0.04;0.1681,0.05;0.171,0.06})

Replies
5
Views
61
Replies
10
Views
227
Replies
4
Views
111
Replies
16
Views
1K
Replies
5
Views
263

### Forum statistics

1,129,763
Messages
5,638,220
Members
417,015
Latest member
ExcelIsEverything

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

### Which adblocker are you using?

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