Multiple arguments for using the if and function for a range betwwen numbers

lpac8

New Member
Joined
May 18, 2017
Messages
2
Hello!

I am hoping someone can help me. Basically, I am trying to have a percentage populate based on the number within a cell. I run into issues with the multiple arguments. This is what I am trying to accomplish...
Anything less than 57000 = 5%
Greater than 57001 but less than 84000 = 4%
Greater than 84001 but less than 150000=3.5%
And anything over 150,999 is 3%

=if(C3>=57000,5%,if(and(C3<=57001,C3>=84000,4%)))

I'm just lost, please help
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If you set up a table with your ranges and rates such that column M will have

0
57001
84001
150999

and column N has

.05
.04
.035
.03

then this lookup will get you what you want

Code:
=INDEX($N$4:$N$7,MATCH(H5,$M$4:$M$7,1))

Where H5 is the value being looked up.

You will need to adjust your ranges.
 
Last edited:
Upvote 0
Welcome to the forum.

Try:

=LOOKUP(C3,{0,57001,84001,150001},{0.05,0.04,0.035,0.03})


Edit: lrobbo's method has the advantage that it's usually easier to change a table on the sheet, than changing a formula.
 
Last edited:
Upvote 0
Hello!

I am hoping someone can help me. Basically, I am trying to have a percentage populate based on the number within a cell. I run into issues with the multiple arguments. This is what I am trying to accomplish...
Anything less than 57000 = 5%
Greater than 57001 but less than 84000 = 4%
Greater than 84001 but less than 150000=3.5%
And anything over 150,999 is 3%

=if(C3>=57000,5%,if(and(C3<=57001,C3>=84000,4%)))

I'm just lost, please help

Or this:

=LOOKUP(C3,{0,57001,84001,150001},{0.05,0.04,0.035,0.03})

And if you wanted to figure out your =IF(), I think you have the logic backwards.

=IF(C3<=57000,5%,IF(AND(C3>=57001,C3<=84000),4% ... etc.
 
Last edited:
Upvote 0
Welcome to the forum.

Try:

=LOOKUP(C3,{0,57001,84001,150001},{0.05,0.04,0.035,0.03})


Edit: lrobbo's method has the advantage that it's usually easier to change a table on the sheet, than changing a formula.

This worked beautifully! You're absolutely right regarding Irobbo's method however. I would like to easily adjust the %'s. Can I set up the table on a different sheet?

I appreciate everyone's help!
 
Upvote 0
Yes, you can set up the table on a different page. Just create the page, make the table, then when you are writing the formula, switch over to the page and select your ranges in the table. Make sure that you have absolute range references, i.e. you have a $ in front of the column and row references.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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
Back
Top