Err 509 OR in SEARCH

Discuss the spreadsheet application

Err 509 OR in SEARCH

Postby morphingstar » Thu Mar 12, 2020 3:04 am

What is wrong with this formula?
Code: Select all   Expand viewCollapse view
=IF(ISERR(SEARCH(OR($C63="Sample";$C63="Leseprobe"))"_Full_";"_Sample"))

Result Err 509

The formula works without the OR-part
Code: Select all   Expand viewCollapse view
=IF(ISERR(SEARCH($C63="Sample"));"_Full_";"_Sample"))

or
Code: Select all   Expand viewCollapse view
=IF(ISERR(SEARCH(" Leseprobe ";$C62));"_Full_";"_Sample_")


Tried
Code: Select all   Expand viewCollapse view
=IF(ISERR(SEARCH(OR($C64="Sample";"Leseprobe"))"_Full_";"_Sample"))

Same result
thanks OR gracias OR danke OR merci OR obrigade
Last edited by RusselB on Thu Mar 12, 2020 7:14 am, edited 1 time in total.
Reason: Tags corrected
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 83
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Err 509 OR in SEARCH

Postby robleyd » Thu Mar 12, 2020 6:55 am

Can you explain what it is you are trying to achieve?
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Err 509 OR in SEARCH

Postby RusselB » Thu Mar 12, 2020 7:21 am

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
Code: Select all   Expand viewCollapse view
=if(iserr(OR($C63="Sample";$C63="Leseprobe"));"_Full_";"_Sample")

The formula you posted is missing a parameter separator between the OR comparisons and the return if the comparison(s) match.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Err 509 OR in SEARCH

Postby Villeroy » Thu Mar 12, 2020 3:23 pm

=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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Err 509 OR in SEARCH

Postby morphingstar » Fri Mar 13, 2020 9:23 pm

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

oo_search_with_OR_20200313a.jpg
oo_search_with_OR_20200313a.jpg


I tried to place picture Inline, don't know why t seems not to do. Duplicate message was not intended.
Attachments
oo_search_with_OR_20200313a.jpg
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 83
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Err 509 OR in SEARCH

Postby Villeroy » Fri Mar 13, 2020 9:37 pm

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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Err 509 OR in SEARCH

Postby morphingstar » Thu Mar 19, 2020 7:35 am

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
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 83
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Err 509 OR in SEARCH

Postby Zizi64 » Thu Mar 19, 2020 8:52 am

morphingstar,

please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9427
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Err 509 OR in SEARCH

Postby Villeroy » Fri Mar 20, 2020 2:31 pm

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_") :?:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Err 509 OR in SEARCH

Postby John_Ha » Fri Mar 20, 2020 3:30 pm

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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Err 509 OR in SEARCH

Postby Lupp » Fri Mar 20, 2020 7:04 pm

Given:
morphingstar wrote:What is wrong with this formula?
Code: Select all   Expand viewCollapse view
=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   Expand viewCollapse view
=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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests