• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

IF formula, more than one arguement?

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
robmarr700
Asked:
robmarr700
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Rory ArchibaldCommented:
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
 
Saqib Husain, SyedEngineerCommented:
For the given case you can also try

=(1+INT(A2/12000))/100*A2
0
 
robmarr700Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
=IF(A2<12000,0,MIN(0.06,(1+INT(A2/12000))/100)*A2)
0
 
Rob HensonFinance AnalystCommented:
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
 
robmarr700Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Rory ArchibaldCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Katie PierceCommented:
=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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now