Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Emmie99
 
Posts: n/a
Default 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!
  #2   Report Post  
DaveB
 
Posts: n/a
Default

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!

  #3   Report Post  
Simon Letten
 
Posts: n/a
Default

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!

  #4   Report Post  
Emmie99
 
Posts: n/a
Default

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!

  #5   Report Post  
DaveB
 
Posts: n/a
Default

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!



  #6   Report Post  
Emmie99
 
Posts: n/a
Default

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"