ISLOGICAL function

[EarlyBirdie]

Cell A1 contains the value "Insurance Claim" and Cell A2 contains the value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance". The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No Claim"
and "Claim" in the respective cells. However, when I change the formulas to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display "No
Claim" for all three cells. I expected to see the same results as generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.

 

[Kevin B]

ISLOGICAL evaluates a cell and returns TRUE if the cell contains TRUE or
FALSE, and FALSE for anything else. ISERROR returns a logical value, but if
your FIND function returns an error ISLOGICAL will return FALSE because and
error is neither TRUE or FALSE.

 

[Peo Sjoblom]

I think you have misunderstood what ISLOGICAL does. It only tests if a cell
or a formula returns a Boolean value and returns TRUE if the cell/result is
TRUE or FALSE and returns FALSE for anything else.
Since FIND returns either a number or an error ISLOGICAL will always return
FALSE. In what way did you think it could help you where ISERROR could not?


--


Regards,


Peo Sjoblom

 

[Roger Govier]

Hi

Find returns the starting position within the cell for the text being found,
1 in cell A1 and 11 in cell A3
If it is not found, then it will return a #VALUE error.

ISLOGICAL can only deal with values which return True or False

I don't understand why using ISERROR makes the formula too complex, compared
with ISLOGICAL
You could use ISNUMBER, but you would need to reverse your outcomes.
=IF(ISNUMBER(FIND("Claim",A1,1)),"Claim","No Claim")

 

[EarlyBirdie]

OK, thanks everyone for your responses. So since using ISLOGICAL will not
provide the desired solution, let me provide more detail to what I am
attempting to do.

Cells in column A will contain text or contain nothing. In column B's
cells, I want to have a formula that checks the adjacent column A cell to see
if its text contains any of five specified six-character strings that may be
present anywhere in the text (there will not be more than one of the values
in each cell) and then display the six-character text that may be found or
indicate that none of the searched-for strings are present. I tried some
ISERROR combinations but can't find a suitable solution.

 

[EarlyBirdie]

OK,I figured out a solution using a series of IF(ISERROR formulas connected
as a string with "&". Thanks again for the explanation on the ISLOGICAL
function though. I always thought it was the opposite of ISERROR.

 

[T. Valko]

Try this:

List the 5 text strings in a range of cells. Assume this range is F2:F6 -

F2 = string1
F3 = string2
F4 = string3
F5 = string4
F6 = string5

Enter this formula in B2 and copy down as needed:

=IF(A2="","",IF(ISNA(LOOKUP(2,1/SEARCH(F$2:F$6,A2),F$2:F$6)),"no
match",LOOKUP(2,1/SEARCH(F$2:F$6,A2),F$2:F$6)))

 

[Ron Rosenfeld]

OK, thanks everyone for your responses. So since using ISLOGICAL will not
provide the desired solution, let me provide more detail to what I am
attempting to do.

Cells in column A will contain text or contain nothing. In column B's
cells, I want to have a formula that checks the adjacent column A cell to see
if its text contains any of five specified six-character strings that may be
present anywhere in the text (there will not be more than one of the values
in each cell) and then display the six-character text that may be found or
indicate that none of the searched-for strings are present. I tried some
ISERROR combinations but can't find a suitable solution.

List your desired strings in a range, and NAME the range "List" (or use the
actual cell reference).

Then try this formula to mimic your first example:

=IF(SUMPRODUCT(-ISNUMBER(FIND(List,A1)))=0,"No Claim","Claim")

Or, to display the actual code specified six-character string:

=INDEX(List,MATCH(TRUE,ISNUMBER(FIND(List,A1)),0))

entered as an **array formula** by holding down <ctrl><shift> while you hit
<enter>.

Formula #2 will give an error if there is no match, so perhaps, to combine the
two:

=IF(SUMPRODUCT(-ISNUMBER(FIND(List,A1)))=0,"No Claim",
INDEX(List,MATCH(TRUE,ISNUMBER(FIND(List,A1)),0)))

also entered as an **array formula**

--ron