Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » Functions and Formulas » Nested IF statement Driving Me Crazy! Please Help

Nested IF statement Driving Me Crazy! Please Help

Previous | Next
Latest post Fri, Jul 30 2010 5:45 PM by Omar. 4 replies.

  • Fri, Jul 30 2010 9:42 AM

    • Mubeen
    • Not Ranked
    • Joined on Fri, Jul 30 2010
    • Posts 1
    • Points 37

    Nested IF statement Driving Me Crazy! Please Help

    Reply |Contact

    Please any1 evaluate and let me know where i am going wrong. All cells produce an OUtcome of J regardless of the amounts being evaluated.

    Using Excel 2007

    =IF(F18<3579,"A",IF(F18<4349,"B",IF(F18<5299,"C",IF(F18<6089,"D",IF(F18<6969,"E",IF(F18<8829,"F",IF(F18<10549,"G",IF(F18<12119,"H",IF(F18<17549,"I",IF(F17>17550,"J",0))))))))))
    • Post Points: 37

  • Fri, Jul 30 2010 10:41 AM In reply to

    • MikeE7
    • Not Ranked
    • Joined on Fri, Jul 30 2010
    • Posts 1
    • Points 36

    Re: Nested IF statement Driving Me Crazy! Please Help

    Reply |Contact |Mark as Answer

    In your last if statement you changed the cell from F18 to F17.

    • Post Points: 36

  • Fri, Jul 30 2010 5:20 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 128
    • Points 1,823

    Re: Nested IF statement Driving Me Crazy! Please Help

    Reply |Contact |Mark as Not Answer

    This is a problem that just cries out for the VLOOKUP function.

    Set up your lookup table like this in two columns. The left column will be a list of all the hurdle values. The right column will be the letters to return. Above you have 10 tests, so you will have a table 10 rows by 2 columns.

     

    0 A
    3579 B
    4349 C
    5299 D
    6089 E
    6969 F
    8829 G
    10549 H
    12119 I
    17549 J

     

    Then use this formula:

    =VLOOKUP(B1,C1:D10,2,TRUE)

    This is just a bit simpler to understand.

    One thing to know. To the left of the A, you will need to put a number that is lower than any possible valid number, or you will get an error.

    Omar Freeman Kitchener, ON
    • Post Points: 5

  • Fri, Jul 30 2010 5:25 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 128
    • Points 1,823

    Re: Nested IF statement Driving Me Crazy! Please Help

    Reply |Contact |Mark as Answer

    Also, the answer will fail if it is exactly 17549 or 17550. These two will return "0". The last IF statement needs to be:

    IF(F18>=17549,"J",0)

    Omar Freeman Kitchener, ON
    • Post Points: 5

  • Fri, Jul 30 2010 5:45 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 128
    • Points 1,823

    Re: Nested IF statement Driving Me Crazy! Please Help

    Reply |Contact |Mark as Answer

    Also, if you do stick with the IF statements, 17549 and 17550 will result in "0" as the answer. You need to change the last IF statement to:

    IF(F18>=17549,"J",0)

    Omar Freeman Kitchener, ON
    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.