+ Reply to Thread
Results 1 to 8 of 8

IF AND troubles

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Westbrook, Maine
    MS-Off Ver
    Excel 2007
    Posts
    15

    IF AND troubles

    I can't figure out what I am doing wrong. Please help.

    • IF K27 is equal to 0, then ELEPHANT,
    • IF K27 is greater than 0, then LION
    • IF K27 is greater than 0 and E45 is greater than 0, then TIGER

    =IF(K27=0, “ELEPHANT”, IF(K27>0, “LION”, IF(AND(K27>0, E45>0), “TIGER”))))

    Why oh why doesn't this work? THANK YOU!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: IF AND troubles

    Your first two criteria have the same condition - which do you want returned; elephant or lion?

    Also the third criteria will only be evaluated if the 1st 2 are false, but it also needs to be true in order for tiger to be returned.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: IF AND troubles

    1) =IF(K27=0, “ELEPHANT”,
    2) IF(K27 > 0, “LION”,
    3) IF(AND(K27>0, E45>0), “TIGER”))))

    3) will only get executed if 2) is FALSE
    but if 2) is FALSE then your AND will not work since the K27 > 0 part of 3) will evaluate to FALSE

    Try this

    =IF(K27=0, “ELEPHANT”, IF(AND(K27>0, E45>0), “TIGER”,IF(K27>0, "LION","OTHER")))))))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    11-17-2010
    Location
    Westbrook, Maine
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: IF AND troubles

    1 and 2 shouldn't be the same. If K27 is 0, then I want to see ELEPHANT. If it's greater than 0, then I want to see LION.

    Special-K, I pasted your formula in, but it read "The Formula you typed contains an error."

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: IF AND troubles

    Special-K's formula has a few too many close brackets.

    =IF(K27=0, "ELEPHANT", IF(AND(K27>0, E45>0), “TIGER”,IF(K27>0, "LION","OTHER")))

    PS Apologies for misreading your requirements - it's been a long day

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: IF AND troubles

    That'll teach me not to check my answers. Try this

    =IF(K27=0,"ELEPHANT",IF(AND(K27>0,E45>0),"TIGER",IF(K27>0,"LION","OTHER")))

  7. #7
    Registered User
    Join Date
    11-17-2010
    Location
    Westbrook, Maine
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: IF AND troubles

    Special-K, you are my hero!!!!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,235

    Re: IF AND troubles

    under what conditions do you want to see TIGER?
    K27=0, “ELEPHANT”
    K27>0, “LION”
    ...(AND(K27>0, E45>0), “TIGER”))))...this will always be supersceded by K27>0, “LION”, becasue both test for K27>0, and K27>0, “LION” comes 1st, so that criteria is met before the 2nd criteria
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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