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

Lmfacc

New Member
Joined
Oct 20, 2020
Messages
2
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 20, 2020
Messages
2
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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})
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

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
Top