IF than statement in a range of numbers? please help?

[AC man]

I am trying to creat an IF statement that will be true if the number falls
within a range of numbers. I want it to say IF A2 is less than 200 but
greater than 100 then this cell will display 6". I only know how to write an
IF statement for a less than or equal to statment but I dont know how to make
it fall in a specific range of numbers. please help thanks.

 

[Pete]

What you need is:

=IF(AND(A2>100,A2<200),6,"out of range")

You don't specify what you want if the condition is not met, but this
produces the text "out of range" - modify as appropriate.

Pete

 

[CLR]

=IF(AND(A2>100,A2<200),6,"")

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

=IF(AND(A2>100,A2<200),6,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[David Billigmeier]

Try this. If your 'Max' number is in A1 (in this case 200), your 'Min'
number is in B1 (in this case 100), and the number you wish to test in C1, in
D1 put this formula:

=IF(AND(C1<A1,C1>B1),6,"")

Does that help?

 

[Sloth]

use the AND function

IF(AND(A2<200,A2>100),6,"Not In Range")

 

[Sloth]

You can also use the + and * symbols to substitute for and & or.

Example:
=IF((A2>100)*(A2<200),6)
=IF((A2>200)+(A2<100),6)

The first one is like saying if A2>100 AND A2<200 then output 6. So if it
is in the range it is TRUE.
The second one is like saying if A2<100 OR A2>200 then output 6. So if it
is out of the range it is TRUE. (I switched the < and > symbols because every
real number is <200 or >100).

 

[AC man]

Thank you, if I want to continue on with the IF stetments how should I write
it.
If the range in A1 is 100-200 I want it to say 6" in A2
If the range in A1 is 201-300 I want it to say 7" in A2
If the range in A1 is 301-400 I want it to say 8" in A2
If the range in A1 is 401-500 I want it to say 9" in A2
and so forth...
What would the code look like, you dont have to write it all the way out.
Just enough so I can get an idea. Thank you.

 

[Jonathan Cooper]

It looks to me like there is a mathamatical relationship to the answer you
want and the number ranges. Try this.

Assuming the number 101 is in cell A1.

=(ROUNDUP(A1,-2)/100)+4

This will turn 101, into a 2, then add 4 which equals 6.

the only problem might be, your range is inconsistent, because your first
range started at 100, and then skipped to 201

 

[CLR]

It depends on how many increments you are going to use........if only a few
(7 or less), you can use a nested IF statement like......
=IF(AND(A2>100,A2<200),6,IF(AND(A2>200,A2<300,7,"")

If you will have "many" increments, you might consider the VLOOKUP function.

Vaya con Dios,
Chuck, CABGx3

 

[Sloth]

=VLOOKUP(A2,{100,6;200,7;300,8;400,9;500,#N/A},2,TRUE)

You can inbed a table like this into the VLOOKUP function, or use an actual
table. You will get an error if A2 is <100 ro >500. If you want to expand
the list (say to 1000+ -> 15) you could do it like this.

=VLOOKUP(A2,{100,6;200,7;300,8;400,9;500,10;600,11;700,12;800,13;900,14;1000,15},2,TRUE)

with this formula anything <100 will result in an error, and anything >1000
is 15. You can use an IF statement to change this.

The VLOOKUP function finds the next smallest number and matches the value in
the table. So 243 will look at 200 and check column 2 of the table resulting
in 7.

Since you have a mathematical relationship, you can probably use a formula
like

=INT(A2/100)+5

This won't be exactly what you wanted (100-199 -> 6, 200-299 -> 7, etc.),
but you can use this formula to get closer

=INT((A2-1)/100)+5

for (101-200 -> 6, 201-300 -> 7, etc.)