# 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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### 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
48
Replies
10
Views
212
Replies
4
Views
99
Replies
16
Views
1K
Replies
5
Views
259

1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

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

### Which adblocker are you using?

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

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