Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Looking for help with IF nested function limit

Latest post Wed, Jul 14 2010 8:55 AM by Alan Hutchins. 2 replies.
  • Mon, Jul 12 2010 1:22 PM

    • mrtoyz
    • Not Ranked
    • Joined on Sat, Jul 10 2010
    • Posts 2
    • Points 58

    Looking for help with IF nested function limit

    I've researched a few options but havent been able to make anything work.
    Here is the formula I know would would if the limit were not 7....

    =IF(C5="AT",K5*9.5%,IF(C5="Cr",K5*9.8%,IF(C5 ="Du",K5*9.8%,IF(C5="OH",K5*9.5%,IF(C5="PWC",K5 *9.5%,IF(C5="Sc",K5*9.8%,IF(C5="Sp",K5*9.8 %,IF(C5="To",K5*9.8%,IF(C5="Tr",K5*9.8%, IF(C5="UTV",K5*9.8%))))))))))

    Well I've added a few more values now totaling 14...

    I tried this and I get a "false" AND then the correct answer, obviously because the correct value is in the second IF statement.

    =IF(C5="ATV S",J5*9.5%,IF(C5="ATV U",J5*9.5%,IF(C5="Cr",J5*9.8%,IF(C5="Cr T",J5*9.8%,IF(C5="DS",J5*9.8%,IF(C5="MX",J5*9.5%,IF(C5="Off", J5*9.5%)))))))&IF(C5="PWC",J5*9.5%,IF(C5="Scr" ,J5*9.8%,IF(C5="Sta",J5*9.8%,IF(C5="Sp",J5 *9.8%,IF(C5="Sp T",J5*9.8%,IF(C5="Tr",J5*9.5%,IF(C5="UTV",J5*9.5%)))))))

    The I tried this, but I get "formula contains an error"

    =LOOKUP(C5,{"ATV S","ATV U","Cruiser","Cr T","DS","MX","Off","PWC","Scr","St"," Sp","Sp T","Tr","UTV"},{J5*9.5%,J5*9.5%,J5*9.8% ,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%,J5*9.5%,J5*9.8%,J 5*9.8%,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%})

    I'd REALLY appreciate any help you can give...I'm stumped.

    -

    • Post Points: 37
  • Mon, Jul 12 2010 2:17 PM In reply to

    • Jim Cone
    • Top 10 Contributor
    • Joined on Tue, Jan 15 2008
    • Portland, Oregon - USA
    • Posts 233
    • Points 3,315

    Re: Looking for help with IF nested function limit

     

    =IF(OR(C5="ATV S",C5="ATV U",
    C5="MX",C5="Off",C5="PWC",C5="Tr",C5="UTV"),J5*9.5%,IF(OR(C5="Cr",C5="Cr T",
    C5="DS",C5="Scr",C5="Sta",C5="Sp",C5="Sp T"),J5*9.8%,"bad entry"))
    '--
    Jim Cone
    Portland, Oregon USA
    http://tinyurl.com/XLCompanion

     

    • Post Points: 5
  • Wed, Jul 14 2010 8:55 AM In reply to

    Re: Looking for help with IF nested function limit

    As an alternative, would it be worthwhile evaluating a VLOOKUP instead?

    One benefit would be that additional values in C would just take 1 change, as would any changes in the %.

     

     

    • Post Points: 5
Page 1 of 1 (3 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.