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

1. 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!  Reply With Quote

2. 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.  Reply With Quote

3. Re: Can I make this equation easier?

Just an alternative:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"715","717","718","94"},\$G1)))," -1"," ")  Reply With Quote

4. Re: Can I make this equation easier?

Here's a Non array entered version.

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

5. 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!  Reply With Quote

6. 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.  Reply With Quote

User Tag List Posting Permissions

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