Likes:  0

# Thread: Getting False Statement. Multiple if

1. ## 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. ## Re: Getting False Statement. Multiple if

that equal sign should be an comma IF

3. ## Re: Getting False Statement. Multiple if

Care to provide a small sample along with the desired results?

4. ## Re: Getting False Statement. Multiple if

actually ignore my question

5. ## 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. ## 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. ## 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. ## Re: Getting False Statement. Multiple if

That works!!! Thanks I appreciate it.

9. ## 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

10. ## 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')

## 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
•