Results 1 to 6 of 6

Thread: Can I make this equation easier?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can I make this equation easier?

    I am writing equations to check a cell for a range of values, which over time, will grow to be quite extensive. If the cell contains any of the specified values, then enter a particular value. I have the cell currently functioning like this:

    =IF(OR(ISNUMBER(SEARCH("715",$G1)),ISNUMBER(SEARCH("717",$G1)),ISNUMBER(SEARCH("718",$G1)),ISNUMBER(SEARCH("94",$G1)))," -1"," ")

    I would like to do something like this instead:

    =IF(ISNUMBER(SEARCH(OR("715","717","718","94"),$G1))," -1"," ")

    This would make things much easier in the future to add new values to my search. The formula does not give an error, but always returns a false value. Any thoughts? Thanks in advance!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I make this equation easier?

    You were close, try this

    =IF(OR(ISNUMBER(SEARCH({"715","717","718","94"},$G1)))," -1"," ")

    but enter it with Ctrl + Shift + Enter, not just Enter.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Can I make this equation easier?

    Just an alternative:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"715","717","718","94"},$G1)))," -1"," ")
    Microsoft MVP - Excel

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,057
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I make this equation easier?

    Here's a Non array entered version.

    =IF(SUM(COUNTIF($G1,"*"&{"715","717","718","94"}&"*"))," -1"," ")
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    Mar 2015
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I make this equation easier?

    So I used Juan's solution, since it was the one I understand the most. Strangely though, it works even without Ctrl+Shift+Enter. Just for my own sake, any ideas why? Also, Andrew and Jonmo, could you explain how your solutions work? I know that they do, but while trying to learn all I can about excel, I'd like to try and understand why. Thanks again!

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,057
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I make this equation easier?

    Sure

    =IF(SUM(COUNTIF($G1,"*"&{"715","717","718","94"}&"*"))," -1"," ")

    Countif is normally used to count how many cells in a range equal a specified value.
    =countif(A1:A10,"hello")
    How many cells in A1:A10 are equal to "hello"

    The * can be used as a wildcard.
    =countif(A1:A10,"*hello*")
    Now it will find hello anywhere within a cell like "I'd say hello, but I don't want to"

    And it doesn't have to be used on a range of cells, it can be used on a single cell as well.
    Very common method to test if a cell 'contains' a substring.
    You can't do =A1="*hello*"
    That would not use the * as a wildcard, it would consider it literally.
    So you can use
    =countif(A1,"*hello*")
    If that returns 1, it found "hello" in A1, 0 means it didn't.

    Now the context of
    =SUM(countif(A1,{"*hello*","*goodbye*"}))
    This basically creates an array of 2 results of the countif. 1 for hello and 1 for goodbye
    The SUM then sums the results of them.

    Then you Test if that result is 0 or Greater than 0
    If it's 0, IF will consider it FALSE, any number other than 0 will be considered TRUE.
    =IF(countif(....),do this, do that)

    Hope that helps.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

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
  •