+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    AC man
    Guest

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

    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.

  2. #2
    Pete
    Guest

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

    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


  3. #3
    David Billigmeier
    Guest

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

    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?
    --
    Regards,
    Dave


    "AC man" wrote:

    > 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.


  4. #4
    Sloth
    Guest

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

    use the AND function

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

    "AC man" wrote:

    > 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.


  5. #5
    CLR
    Guest

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

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

    Vaya con Dios,
    Chuck, CABGx3



    "AC man" wrote:

    > 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.


  6. #6
    Bob Phillips
    Guest

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

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

    --

    HTH

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


    "AC man" <AC man@discussions.microsoft.com> wrote in message
    news:28E15155-6972-44E9-9701-D445D12708EA@microsoft.com...
    > 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.




  7. #7
    Sloth
    Guest

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

    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" wrote:

    > 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.


  8. #8
    AC man
    Guest

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

    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.


    "CLR" wrote:

    > =IF(AND(A2>100,A2<200),6,"")
    >
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "AC man" wrote:
    >
    > > 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.


  9. #9
    Jonathan Cooper
    Guest

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

    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

    "AC man" wrote:

    > 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.
    >
    >
    > "CLR" wrote:
    >
    > > =IF(AND(A2>100,A2<200),6,"")
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "AC man" wrote:
    > >
    > > > 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.


  10. #10
    CLR
    Guest

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

    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


    "AC man" wrote:

    > 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.
    >
    >
    > "CLR" wrote:
    >
    > > =IF(AND(A2>100,A2<200),6,"")
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "AC man" wrote:
    > >
    > > > 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.


  11. #11
    Sloth
    Guest

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

    =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.)

    "AC man" wrote:

    > 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.
    >
    >
    > "CLR" wrote:
    >
    > > =IF(AND(A2>100,A2<200),6,"")
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "AC man" wrote:
    > >
    > > > 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1