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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,965
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:

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,126
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:

lpac8

New Member
Joined
May 18, 2017
Messages
2
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!
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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.
 

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top