Power Query Course in Spanish
Likes Likes:  0
Results 1 to 10 of 10

Thread: Getting False Statement. Multiple if

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Getting False Statement. Multiple if

    If am attempting to create multiple if statements. I need excel to search for a specific word in one cell and change the criteria in other cell for greater than. If I do the beginning of the formula I have no problem. However when I change the search to another word it no longer works.

    =IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"))=IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND( Sheet1!S2>Sheet3!I19),"Pass","Fail"))

    This formula above I get ends with a False statement which is not working. However if I use the Formula below it will work. I need to expand upon this formula to work like I have above. I have a total of 9 different words that I need it to search.

    =IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"))

    Any help would be much appreciative. Thanks

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,009
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting False Statement. Multiple if

    that equal sign should be an comma IF
    Last edited by mole999; Feb 11th, 2018 at 11:46 AM.
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,412
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Getting False Statement. Multiple if

    Care to provide a small sample along with the desired results?
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,009
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting False Statement. Multiple if

    actually ignore my question
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,710
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Getting False Statement. Multiple if

    The top formula is asking if two values are the same and will return only True or False.


    [ IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail")) ] = [ IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND( Sheet1!S2>Sheet3!I19),"Pass","Fail")) ]

    The left hand side of that, IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail")), will return either "Pass", "Fail", or False.
    If I2 has the substring "11M", then it will return "Pass" or "Fail"; otherwise False.
    Similarly, if I2 has the substring "5M", the right side will return "Pass" or "Fail"; otherwise False.

    If I2 contains both of those substrings, then the whole might evaluate to True.
    IF I2 contains only one of one substrings, then the whole will evaluate to False.
    If I2 contains neither of those substrings, then the whole will evaluate to True.

    If you want the right side of the top formula to be the IF False argument of the IF, move a parenthesis.

  6. #6
    New Member
    Join Date
    Jan 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting False Statement. Multiple if

    If I change the 2nd = to a comma then I get a #Value !

    What I am looking to achieve is changing the word in cell I2 from 3m to 5M or 7M or 11M or 23M or 36M or 52M or 102M then it will calculate another figure in another cell. I.e. if I select 5M S2 might have an response with 600. I need to then verify that the end value in S2 is greater than 500 to pass. If it is a 3M then it needs to be greater than 300, 7M greater than 700, 11M greater than 1100 so on and so forth.

    Size Fl Rate Pass / Fail
    5M 20 1800 Pass

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,710
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Getting False Statement. Multiple if

    try
    =IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"), IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND( Sheet1!S2>Sheet3!I19),"Pass","Fail")))

  8. #8
    New Member
    Join Date
    Jan 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting False Statement. Multiple if

    That works!!! Thanks I appreciate it.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,412
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Getting False Statement. Multiple if

    Why do you need AND?

    The following are the same thing...

    IF(AND(Sheet1!S2>Sheet3!I21)

    ==

    IF(Sheet1!S2>Sheet3!I21

    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,710
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Getting False Statement. Multiple if

    =IF(Sheet1!S2 < IF(ISNUMBER(SEARCH("11M",Sheet1!I2)), Sheet3!I21, IF(ISNUMBER(SEARCH("5M",Sheet1!I2)), -Sheet3!I19))), "Pass", "Fail')
    Last edited by mikerickson; Feb 11th, 2018 at 01:11 PM.

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
  •