Using if and or
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Using if and or

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 12:20, dneas wrote:
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))
    Try this:

    =IF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<200,"Class III")))

    Hope this helps,

    Russell

    [ This Message was edited by: Russell Hauf on 2002-03-11 12:27 ]

    [ This Message was edited by: Russell Hauf on 2002-03-11 12:27 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,821
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-11 12:20, dneas wrote:
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))
    =IF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<200,"Class III","?")))

    This will classify F2=0 as Class I. If that's not desired, use

    or

    =IF(F2,IF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<200,"Class III","?"))),"")

    Both formulas will produce a question mark if F2 >= 200.




    [ This Message was edited by: Aladin Akyurek on 2002-03-11 13:02 ]

  4. #4
    Guest

    Default

    On 2002-03-11 12:20, dneas wrote:
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))
    try this formula
    =iF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<200,"Class III")))

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,821
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-11 12:20, dneas wrote:
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))
    Also nice to have:

    =IF(F2>0,VLOOKUP(F2,{0,"Class I";73,"Class II";100,"Class III";200,"?"},2),"")

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Russell - I get an error message with that formula??

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anonymous - if the number is 200 it returns Class II because it is greater than 100. I think you need the OR or AND included.

    [ This Message was edited by: dneas on 2002-03-11 12:55 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,821
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-11 12:54, dneas wrote:
    Anonymous - if the number is 200 it returns Class II because it is greater than 100. I think you need the OR or AND included.

    [ This Message was edited by: dneas on 2002-03-11 12:55 ]
    Try also both formulas I proposed. If the input is 200, both return a question mark. You can replace ? with something that is more suitable.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 12:32, Anonymous wrote:
    On 2002-03-11 12:20, dneas wrote:
    F2=104 but I cannot get this to work? =IF(F2<73,"Class I",IF(F2>73<100,"Class II",IF(F2>100<200,"Class III")))
    try this formula
    =iF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<200,"Class III")))
    On 2002-03-11 12:54, dneas wrote:
    Anonymous - if the number is 200 it returns Class II because it is greater than 100. I think you need the OR or AND included.

    [ This Message was edited by: dneas on 2002-03-11 12:55 ]
    dneas, if you want Class II returned for the number 200, change the < to a <=:
    =iF(F2<73,"Class I",IF(F2<100,"Class II",IF(F2<=200,"Class III"))).

    If statements are progressive, meaning it first checks if F2 is less than 73, if not, it then checks if F2 is less than 100, but it already knows its greater than 73 because this was already checked. It then checks if F2 is less than or equal to 200, knowing that it is greater than 200. You should also think about putting another else at the end, in the case that F2 is greater than 200.

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I meant if you want "Class III", not Class II like I wrote

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com