Formula Evaluates To Zero

[goss]

Hi all,

Using Excel 2010.

This formula is returning zero
=IF(ISERROR(AND(FIND("RABBIT",B18),FIND("FOX",B18))),0,1)

The first part, FIND("RABBIT",B18) evaluates to 5 when I highlight and hit [F9] in the formula bar

The second part, FIND("FOX",B18)evaluates to #VALUE! when I highlight and hit [F9] in the formula bar

It appears that a value of 1 would be correct answer since both conditions do not return an error.

What might I be missing here?

 

[T. Valko]

Hi all,

Using Excel 2010.

This formula is returning zero
=IF(ISERROR(AND(FIND("RABBIT",B18),FIND("FOX",B18))),0,1)

The first part, FIND("RABBIT",B18) evaluates to 5 when I highlight and hit [F9] in the formula bar

The second part, FIND("FOX",B18)evaluates to #VALUE! when I highlight and hit [F9] in the formula bar

It appears that a value of 1 would be correct answer since both conditions do not return an error.

What might I be missing here?

It's because with AND all the arguments must be TRUE.

Try it like this:

=IF(COUNT(FIND({"RABBIT","FOX"},B18)),1,0)

 

[Norie]

Isn't #VALUE! an error?

 

[goss]

Thanks Biff and Norie,

Biff your formula works - thanks.

My formula did not work because I have the the ISERROR and the AND condition juxtaposed.

My correct fomula is:
=IF(AND(ISERROR(FIND("RABBIT",B18)),ISERROR(FIND("FOX",B18))),0,1)

But Biff's formulas is better - more succinct.

 

[T. Valko]

Thanks Biff and Norie,

Biff your formula works - thanks.

My formula did not work because I have the the ISERROR and the AND condition juxtaposed.

My correct fomula is:
=IF(AND(ISERROR(FIND("RABBIT",B18)),ISERROR(FIND("FOX",B18))),0,1)

But Biff's formulas is better - more succinct.

You're welcome. Thanks for the feedback!