Given:
morphingstar wrote:
What is wrong with this formula?
Code: Select all
=IF(ISERR(SEARCH(OR($C63="Sample";$C63="Leseprobe"))"_Full_";"_Sample"))
Result Err 509
Short answer: Everything.
To more detail:
Since the formula parser first looks for
syntactical errors and only reports the first error it finds, there may be additional
syntactical errors, and in addition, of course, functional/semantical errors.
There are.
First the purely syntactical part (errors numbered and described in short between angle brackets):
Code: Select all
=IF(ISERR(SEARCH(OR($C64="Sample";$C64="Leseprobe")<1::missing separator ;><2::missing parameter>))<3::missing operator or separator, ';' e.g.>"_Full_" ; "_Sample") <4::partenthese in excess)>
Then there is an aesthetical error: The first string constant probably returned has a trailing underscore and the seconed hasn't. Why?
Now some semantic errors (partly also syntactical in a wider sense) on the level of the used functions and sub-expressions:
SEARCH() needs two parameters, both being specified as 'String'. The result of a (correct) call to
OR() is
logical (Boolean). That's
TRUE or
FALSE. Following the (evil) general principle of automatic conversion, this result, being returned to a 'String' parameter position, will be converted into text. For reasons I don't understand, Boolean results otherwise shown as words are treated here a 'General' numbers due to their internal representation, and therefore converted to "1" and "0" respectively.
The
SEARCH() part of the formula should therefore look for the occurence of the character "1" or "0" (depending) in the other string. What other string? You are right
this other string was completely missing.
Conclusion 1: The OriginalQuestioner didn't mean a logical operation at all when using OR(). He expected it to be interpreted in a "linguistic" way. This was an illusion. So far about te "String-To-Find".
Conclusion 2: The missing of a "String-To-Search-In" was ignored for untraceable reasons. The referenced content of C64 isn't in an acceptable place.
Finally:
There are differenet solutions I might consider to suggest. Which one(s) to supply here depends on the answer of the OQer to this question:
Shall the comparison of the two special strings "Leseprobe" and "Sample" with the content of the referred cell be
exact OR shall it look for an occurrence of the string inside of a probably much longer string contained in that cell OR shall it be made only case insensitive or shall it be "softened" under both these aspects.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München