Results 1 to 9 of 9

Thread: IFs, ANDs, and BUTs
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question IFs, ANDs, and BUTs

    Need help finding a formula for the following conditions:

    If AV14="Yes" AND AV76 is a number, then enter the value of AV76, BUT if AV14="No" then enter "N/A".

    Any help would be appreciated. Thank you!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,800
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    What if AV14 is "Yes", but AV76 is NOT a number?

    If that is to be "N/A" also, then try:
    Code:
    =IF(AND(AV14="Yes",ISNUMBER(AV76)),AV76,"N/A")
    Last edited by Joe4; Aug 8th, 2019 at 10:16 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    Thanks for that, Joe4. If the other conditions from my first post are met but if AV76 is NOT a number then the cell could say "Hold". If AV14="No", then it doesn't matter what's in AV76. The cell with the formula needs to say "N/A".

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,196
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    This should work

    =IF(AV14="Yes",IF(ISNUMBER(AV76),AV76,"Hold"),"N/A")

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,800
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    Special-K99s modification should do what you want.

    In the future, be sure to include all the conditions in your questions, or else the answers you get might not work for you!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    Thank you for your response and advice, Special-K99. Unfortunately, your solution did not work, either. AV14="Yes", AV76 is not a number, but the response populated with "N/A". If AV14="Yes", the cell should not contain "N/A" regardless of what is in AV76.

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,196
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    Quote Originally Posted by Karlisa View Post
    Thank you for your response and advice, Special-K99. Unfortunately, your solution did not work, either. AV14="Yes", AV76 is not a number, but the response populated with "N/A". If AV14="Yes", the cell should not contain "N/A" regardless of what is in AV76.
    Nope, that's impossible.
    If you look at the second IF condition, the ISNUMBER() condition, the only possible results for TRUE and FALSE are AV76 or "Hold".
    The only way for the result to be N/A is if AV14 is NOT "Yes"

    In a blank cell put =(AV14="Yes").
    It should say TRUE.
    If it says FALSE then AV14 is NOT "Yes" (no matter what it looks like).
    (You probably have a space or CHAR(160) after "Yes" - CHAR(160) is common when data has been copied from the web.

    If you're still having trouble upload the file to an online storage site and post a link to it so we can examine the data.
    Last edited by Special-K99; Aug 8th, 2019 at 11:32 AM.

  8. #8
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    To see if there's any trailing spaces you can try =LEN(AV14) - LEN(TRIM(AV14)), this should return 0 if the cell is clean, i.e. ="yes" exactly, otherwise if it returns 1 or greater then you will have "yes " etc!
    Last edited by tyija1995; Aug 8th, 2019 at 12:11 PM.
    √-1 2³ ∑ π
    …And it was delicious!

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help with IFs, ANDs, and BUTs

    Quote Originally Posted by Special-K99 View Post
    Nope, that's impossible.
    If you look at the second IF condition, the ISNUMBER() condition, the only possible results for TRUE and FALSE are AV76 or "Hold".
    The only way for the result to be N/A is if AV14 is NOT "Yes"

    In a blank cell put =(AV14="Yes").
    It should say TRUE.
    If it says FALSE then AV14 is NOT "Yes" (no matter what it looks like).
    (You probably have a space or CHAR(160) after "Yes" - CHAR(160) is common when data has been copied from the web.

    If you're still having trouble upload the file to an online storage site and post a link to it so we can examine the data.

    Thank you again for your assistance. With trial and error based on your input, I believe I have figured it out with the following formula:

    =IF(AND(ISNUMBER(C76),C14="Yes"),C76,IF(C14<>"Yes","N/A","Hold"))

Some videos you may like

User Tag List

Tags for this Thread

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
  •