that equal sign should be an comma 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
that equal sign should be an comma IF
Last edited by mole999; Feb 11th, 2018 at 12:46 PM.
- 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
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.
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
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.
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
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")))
That works!!! Thanks I appreciate it.
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.
=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 02:11 PM.
Like this thread? Share it with others