IF formula breaks down when result is false

[amanda]

I'm trying to troubleshoot an IF statement that works fine when it is true but gives me a #VALUE! error when false. Even at its most simple, the formula breaks down. I want to do one thing if the first field contains a space, something else if there is no space in that field. Here are two variations I'm working with:

Code: Select all

=IF(FIND(" ";C142);C142;C142)

Code: Select all

=IF(SEARCH(" ";C142);C142;C142)

If there is a space in C142, it prints the contents of that cell just fine. When there is no space however, Calc objects to the format:

Code: Select all

J King   |      J King
S Hinds  |      S Hinds
Narcisse |	#VALUE!

This seems like a bug at this point, or am I doing something wrong still?

[Villeroy]

Code: Select all

=IF(FIND(" ";C142);C142;C142)

This formula will give you the content of C142 regardless if there is a space in C142 or not. Start with =FIND(" ";C142) alone. You will notice that this function returns #VALUE if there is no space in C142, otherwise it returns the position within C142 where the first " " is found. You can convert this to a boolean value:
=ISNUMBER(FIND(" ";C142))
or a position:
=IF(ISNUMBER(FIND(" ";C142));FIND(" ";C142);0) [zero for no match]

[amanda]

Yeah. I should have said that I know it will give me the same thing whether or not the space is found. That is what seems odd, that I can't even make that work.

This is what I'm really trying to get to:

Code: Select all

=IF(AND(FIND(E1;E2);FIND(F1;F2));D1;SUM(D1;1))

If columns E and F match the values above, use value D here. If they don't match, add one to D.

If I just use the AND statement

Code: Select all

=AND(FIND(E1;E2);FIND(F1;F2))

it returns TRUE if the lines match and #VALUE! if they don't. This is only half solved. Ithink the formula I really wanted in this case was "EXACT" not "FIND" or "MATCH".

Code: Select all

=IF(AND(EXACT(E2;E1);EXACT(F2;F1));D1;SUM(D1;1))

However, I'm still confused as to why IF would return a value when it is true and an objection like #VALUE! when it is false.

[squenson]

However, I'm still confused as to why IF would return a value when it is true and an objection like #VALUE! when it is false.

As Villeroy wrote, when the FIND function does not find the string, it returns #VALUE! and in such case Calc stops evaluating the remaining part of your formula and simply displays #VALUE!

This is why you have to consider this case by using a function like ISNUMBER which traps such errors. ISNUMBER(#VALUE!) is FALSE, and then the IF statement can be processed.