Results 1 to 5 of 5

Thread: If else formula

  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    I am finding trouble using IF else formula.....First of all, I want to know ..whether there is any limit of If else formula in excel 2003..???


    If not please check..the formulabelow...


    =IF(OR(B2="CT",B2="ME",B2="MA",B2="NH",B2="NJ",B2= "NY",B2="RI",B2="VT"),"R1",IF(OR(B2="FL",B2="VI",B 2="PR"),"R2",IF(OR(B2="DE",B2="DC",B2="MD",B2="NC" ,B2="PA",B2="VA",B2="WV"),"R3",IF(OR(B2="AL",B2="G A",B2="KY",B2="SC",B2="TN",B2="MS"),"R4",IF(OR(B2= "OH",B2="IL",B2="IN",B2="MI",B2="MN",B2="WI"),"R5" ,IF(OR(B2="TX"),"R6",IF(OR(B2="AR",B2="IA",B2="KS" ,B2="LA",B2="MO",B2="NE",B2="OK"),"R7",IF(OR(B2="C A",B2="HI",B2="MT",B2="NV",B2="ND",B2="SD"),"R8",I F(OR(B2="AK",B2="AZ",B2="CO",B2="ID",B2="NM",B2="O R",B2="UT",B2="WA",B2="WY"),"R9","NEW QUE")))))))))



    ITS WORKING FINE IN EXCEL 2007 BUT ITS NOT WORKING IN EXCEL 2003...PLEASE PROVIDE ME ANY SOLUTION FOR THIS ..


    THANKS AND REGARDS,
    ANKIT

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is a limit of 7 nests in 2003 while the nest limit in 2007 is 64.

    I would suggest making a table with each of the the state codes in the first column and the corresponding code in the second column and then use a formula like the following.

    =if(isna(vlookup(b2,range,2,false)),"NEW QUE",vlookup(b2,range,2,false))

    where "range" is the table of states and codes

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for replying Mike.....and suggesting me that formula ..but I have already tried it ....My problem is whenever I put any formula like I made earlier ( nested if ) ....it should reflect the code of those particular set of states....In your provided formula...evrything is fine..but the problem is ...it will reflect the code of the corresponding next cell....it will not reflect the code of the state to which that state belong to .... for ex....my state is ME and NY and my code for these states is R1 but if in front of these states...there is a value..R3...then your formula will reflect only R3 not R1......

    That's the problem ........


    If anyone can help in any macro or any other solution for this problem ....I'll thankful to him ...


    Thanks and Regards,
    Ankit

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula I provided will return a corresponding R code for the state entered. If the data in B2 is not in the table then NEW QUE will be returned. Please see the attached file for a demonstration using the information in your if statement.


    If this does not solve your problem, please provide a stripped down example of your sheet.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks a Million Mike ........great job done....

Posting Permissions

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