|
Reverse false and combine with true true value
I am trying to create a formula as follows:
Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
Try taking out the AND() statement, you don't need it because you are
multiplying each boolean expression in your formula. Then, the only time you will get a TRUE statement is if each ISNUMBER() returns TRUE. For example: TRUE*TRUE = TRUE TRUE*FALSE = FALSE FALSE*FALSE = FALSE -- Regards, DavidB "Emmie99" wrote: I am trying to create a formula as follows: Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
Emmie99
The NOT function reverses TRUE/FALSE so try IF(AND(ISNUMBER(FIND("Plant",B2)),NOT(ISNUMBER(SEA RCH("Fines",B3)))),"Include","N/A") -- Simon "Emmie99" wrote: I am trying to create a formula as follows: Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
Hi DaveB,
Sorry but this does not solve the problem. The real problem I have is with the reverse false statement. I cannot get to exclude the word "Fines" in cell B3.... Any other alternative? Thanks and regards, "DaveB" wrote: Try taking out the AND() statement, you don't need it because you are multiplying each boolean expression in your formula. Then, the only time you will get a TRUE statement is if each ISNUMBER() returns TRUE. For example: TRUE*TRUE = TRUE TRUE*FALSE = FALSE FALSE*FALSE = FALSE -- Regards, DavidB "Emmie99" wrote: I am trying to create a formula as follows: Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
Oh sorry about that I mis-understood. Ya as Simon said the NOT() function
reverses boolean values, so TRUE becomes FALSE and FALSE becomes TRUE. You can still take out the AND() function, though, if you are multiplying each boolean value together. If you want to keep it in replace the multiplication (*) with a comma (,) -- Regards, DavidB "Emmie99" wrote: Hi DaveB, Sorry but this does not solve the problem. The real problem I have is with the reverse false statement. I cannot get to exclude the word "Fines" in cell B3.... Any other alternative? Thanks and regards, "DaveB" wrote: Try taking out the AND() statement, you don't need it because you are multiplying each boolean expression in your formula. Then, the only time you will get a TRUE statement is if each ISNUMBER() returns TRUE. For example: TRUE*TRUE = TRUE TRUE*FALSE = FALSE FALSE*FALSE = FALSE -- Regards, DavidB "Emmie99" wrote: I am trying to create a formula as follows: Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
Thanks a million! Solved!
"Simon Letten" wrote: Emmie99 The NOT function reverses TRUE/FALSE so try IF(AND(ISNUMBER(FIND("Plant",B2)),NOT(ISNUMBER(SEA RCH("Fines",B3)))),"Include","N/A") -- Simon "Emmie99" wrote: I am trying to create a formula as follows: Cell B2: should read "Plant" Cell B3: should not contain word "Fines" Managed to create a formula that gives me the right output when both are conditions are met, though having problem reversing the false into true for Cell B3 Formula created is as follows: IF(AND(ISNUMBER(FIND("Plant",B2))*ISNUMBER(SEARCH( "Fines",B3))),"Include","N/A") Where do I go wrong? Thanks a lot! |
| All times are GMT +1. The time now is 01:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com