Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IF formula, more than one arguement?

Posted on 2015-01-14
10
Medium Priority
?
145 Views
Last Modified: 2015-01-15
How would I translate the argument below into a formula?

IF A2 => 12000 but < 24000 then return A2*0.02
IF A2=>24000 but < 36000 then return A2*0.03
IF A2 =>36000 but < 48000 then return A2*0.04
IF A2 =>48000 but < 60000 then return A2*0.05


Hypothetically speaking cell A2 could be populated "1369"

I'm intending on copying the formula down for all values in column 2

Thanks Rob
0
Comment
Question by:robmarr700
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40548662
Not sure what you want if it's outside those ranges, but you can use LOOKUP:

=IF(OR(A2<12000,A2>=60000),"",A2*LOOKUP(A2,{12000,24000,36000,48000},{0.02,0.03,0.04,0.05})
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40548684
For the given case you can also try

=(1+INT(A2/12000))/100*A2
0
 

Author Comment

by:robmarr700
ID: 40548692
Good Point Rory see the amended argument below

how would the formula look now?

IF A2<12000 then return A2*0
IF A2 => 12000 but < 24000 then return A2*0.02
IF A2=>24000 but < 36000 then return A2*0.03
IF A2 =>36000 but < 48000 then return A2*0.04
IF A2 =>48000 but < 60000 then return A2*0.05
IF A2 =>60000 return A2*0.06

Rob
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40548699
=IF(A2<12000,0,MIN(0.06,(1+INT(A2/12000))/100)*A2)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40548704
Similar vain to Rory but using a lookup on a separate table of values:

=A2*VLOOKUP(A2,$A$3:$B$8,2)

With A3:B8 being:

-9999      0
12000      0.02
24000      0.03
36000      0.04
48000      0.05
60000      0.06

Thanks
Rob H
0
 

Author Comment

by:robmarr700
ID: 40548706
What would the full formula be? Also bear in mind the "A2*" element will not always be at the fixed intervals.

In the future they could be 0.1, 0.04, 0.37 (example)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40548715
What I posted would be the full formula.

The range A3:B8 would be set elsewhere in your workbook, I merely used it on a blank sheet. When the required values change, you would just change them in the table rather than having to adjust all formulas.

The lookup formula normally has four parameters:
1) Lookup Value - what you are looking for
2) Lookup Range - the range that you are looking in
3) Offset Value - the column that has the result you want
4) Lookup type - False will try to find an exact match, True or omitted (as I have done) will find an approximate match that is not greater than the lookup value, eg 12500 would stop at 12000 as next option is 24000 so is greater than lookup value.

Copied down the reference to A2 will change but the reference to the table won't as I have made it an absolute reference.

Thanks
Rob H
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1000 total points
ID: 40548732
If 0 is the lowest number you'll have:

=A2*LOOKUP(A2,{0,12000,24000,36000,48000,60000},{0,0.02,0.03,0.04,0.050.06})

but I would agree that putting the values into a range would make maintenance simpler as you can just alter the lookup table and all your formulas will follow automatically.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 40548792
Rob,

Just for clarity; your original question says:

I'm intending on copying the formula down for all values in column 2
The current formula refers to column A which is column 1. Can you clarify what you are planning to do for other values? If it should be column 2 or B, then simply change the formula to:

=B2*VLOOKUP(B2,$A$3:$B$8,2)

Then copy down in whichever column you are putting this.

Thanks
Rob H
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40549189
=IF(A2<12000,A2*0,IF(A2<24000,A2*0.02,IF(A2<36000,A2*0.03,IF(A2<48000,A2*0.04,IF(A2<6000,A2*0.05,A2*0.06)))))
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question