Apache OpenOffice Community Forum

What is wrong with this formula? Result Err 509

The formula works without the OR-part or Tried Same result
thanks OR gracias OR danke OR merci OR obrigade

Can you explain what it is you are trying to achieve?

If you're going to use the SEARCH function, use the proper parameters.
Your example is a simple comparison, which isn't the correct usage of the SEARCH function.
As a simple comparison you could use The formula you posted is missing a parameter separator between the OR comparisons and the return if the comparison(s) match.

=IF(isnumber(search("leseprobe";$C63))+isnumber(search("sample";$C63));"_Sample";"_Full_")
isnumber(...)+isnumber(...) returns 1 if either one is true, 2 if both are true and 0 if both are false. Any number <>0 is equivalent to TRUE and lets the IF function return the 2nd argument "_Sample", in case of 0 the 3rd argument "_Full_".
SEARCH is case insensitive. For case sensitive substring matching you can use FIND.

Russel
tks
if(iserr(OR($C63="Sample";$C63="Leseprobe"));"_Full_";"_Sample")
result not correct, if both texts are missing the result should display: Full

here is search string
Die Lösung des großen Pyramiden-Rätsels Sample Helge Jürgens 4. Mai 2019 0
1) search for Sample or Leseprobe, if one found display: Sample
2) search for Sample or Leseprobe, if NONE found display: Full
I tried to place picture Inline, don't know why t seems not to do. Duplicate message was not intended.

OR($C63="Sample";$C63="Leseprobe") never iserr because none of the comparisons will ever raise an error unless there is an error in C63. And since it is never any error, it will always return "_Sample"

I believe you, though I don't really understand the basic ISERR matter as applied to 1 word, I just used the found formula for 1 word, this worked.
Since I have 1 of 2 words to care about, or none, the question now is how can I "IF" and "OR" the matter.
There are many words in the 1 cell, >2.

Given 1 of 2 words exist in searched cell:
IF exists word1 OR IF exists word2 display _Someword_; _Noword_
Given 0 of 2 words exist in searched cell:
IF exists word1 OR IF exists word2 display _Someword_; _Noword_
Result should be colored red here in forum, not in formula.
Can someone help me? I can not handle macros. Does LibreOffice have a function for this?
Thanks

morphingstar,

please upload your ODF type sample file here.

if(iserr(OR($C63="Sample";$C63="Leseprobe"));"_Full_";"_Sample")

Which part of OR($C63="Sample";$C63="Leseprobe") will ever return any error? None. SInce there won't be any error, ISERROR will always return False and IF will always return "_Sample"

What's wrong with =IF(isnumber(search("leseprobe";$C63))+isnumber(search("sample";$C63));"_Sample";"_Full_")

See Calc Functions listed by category for definitions of functions and examples of their use. eg ISERR says:

ISERR

Tests for an error value other than #N/A.
Syntax:

ISERR(value)

Returns TRUE if value refers to or evaluates to an error value other than the Not Available error #N/A, and FALSE otherwise.

Use the ISERROR function to test for any errors including #N/A.

Example:

ISERR(SQRT(-1))

returns TRUE, because taking the squareroot of -1 is an error.

ISERR(C5)

where C5 contains 123, returns FALSE, because 123 is not an error.

ISERR(NA())

returns FALSE, because the error value #N/A is ignored by this function.

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): 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.