IF Formula's using ranges?

[dkliedk]

I am trying to find a way that can automate using a number of ranges. For example

Ex.
If cell A1 is 25-45 A2 is 6
if cell A1 is 45-65 or 15-25 A2 is a 7
If cell A1 is 100-150 or 4-8 A2 is 10

How would a create an IF formula to do this? Or is there another formula that would be better?

 

[korhan]

Is this what you want? There is a logic problem with your formula since you are testing twice for 45. I have changed the 25-45 to L2>=25 AND L2<45.
Anyways, hope I didn't misunderstand what you wanted.

=IF(AND(L2>=25,L2<45),"6",IF(OR(AND(L2>=45,L2<=65),AND(L2>=15,L2<=25)),"7",IF(OR(AND(L2>=100,L2<=150),AND(L2>=4,L2<=8)),"10")))

 

[peterhinton]

=IF(A1="","",IF(A1<26,7,IF(A1<46,6,IF(A1<66,7,IF(A1<101,"nothing Set",IF(A1<151,10,"Nothing Set"))))))

you have not defined what would happen for the values between 65-100, and 8-15 and 0-4, but use something like the above to create what you need

 

[dkliedk]

Essentially I am look for how to use a formula to automate when

25-45 then 1
45-65 or 15-25 then 2
65-100 or 8-15 is 3
100-150 or 4-8 then 4
150+ or <4 then 5

If that helps

 

[Mackers]

The best way of doing this would be using a lookup table with an inexact match.

So, given that semi colons indicate the second column, your table would look like this:

4; 10
9; "unknown"
15; 7
25; 6
45; 7
66; "unknown"
100; 10
151; "unknown"

Let's say this table is in E2:F9. Then your formula is:

=VLOOKUP(A2,$E$2:$F$9,2,1)

Edit: I didn't see your updated list of requirements, but you can adjust the lookup accordingly. Basically the first column should be a lowest-to-highest list of break points, then the second column is the corresponding result.

 

[godsaaint]

You could use AND/OR functions inside an IF formula, but I would just recommend using an ascending order.

Following the example you provided but with more ranges:

1-3 is a 5
4-8 is a 10
9-14 is a 6
15-25 is a 7
25-45 is a 6
etc

You could then insert in B1: =IF(A1<=3,5,IF(A1<=8,10,IF(A1<=14,6,IF(A1<=25,7,IF(A1<=45,6......... keep going until you cover all you bases, and then put enough ))) to close all the IFs.

 

[dkliedk]

Unfortunately I am bound to those ranges due to what they are helping me with

 

[JLGWhiz]

If cell A1 is 25-45 A2 is 6

=IF(AND(A1>=25,A1<=45),6,"")

if cell A1 is 45-65 or 15-25 A2 is a 7

=IF(OR(AND(A1>=45,A1<=65),AND(A1>=15,A1<=25)),7,"")

If cell A1 is 100-150 or 4-8 A2 is 10

=IF(OR(AND(A1>=100,A1<=150),AND(A1>=4,A1<=8)),10,"")

formula goes in A2

 

[korhan]

Try this but before change all the cell references to A1 and of course place this formula under cell A2

=IF(AND(L2>=25,L2<45),"6",IF(OR(AND(L2>=45,L2<=65),AND(L2>=15,L2<=25)),"7",IF(OR(AND(L2>=100,L2<=150),AND(L2>=4,L2<=8)),"10",IF(OR(L2>150,L2<=4),"5","Not in range"))))