We will be migrating from Ask to Discourse on the first week of August, read the details here

# No error in nested function when forgot the function separator [closed]

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hi,

When I use the formulas below, I will get a strange result when I forgot the function separator and is don't know why there is no error is shown.

When I use this formula what in my opinion is wrong there no error shown, I miss the function separator before the second NOT function. The result TRUE or FALSE is given, based on the last part of the second NOT function.

=AND(NOT(IFERROR(IF(SEARCH(":",$F11)>=SEARCH("U",$F11),1,0),0))NOT(IFERROR(IF(SEARCH(":",$F11)>=SEARCH("F",$F11),1,0),0)))

When I remove both NOT functions the formula gives: Err:508 this is correct!

=AND(IFERROR(IF(SEARCH(":",$F12)>=SEARCH("U",$F12),1,0),0)IFERROR(IF(SEARCH(":",$F12)>=SEARCH("F",$F12),1,0),0))

When I put extra bracket add the NOT functions there is an error (Err:508) shown...

=AND((NOT(IFERROR(IF(SEARCH(":",$F11)>=SEARCH("U",$F11),1,0),0)))(NOT(IFERROR(IF(SEARCH(":",$F11)>=SEARCH("F",$F11),1,0),0))))

My question about it, which formula is wrong the first or the last one or how should I use it in this situation?

Thank you.

Libreoffice Version: 6.0.1.1 Build ID: 6.0.1-1 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: kde4; Locale: en-US (nl_NL.UTF-8); Calc: group threaded

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-30 22:22:43.592388

Posting questions as wiki is not well proven. Post using your own identity. Any contributor here with enough "karma" can edit (clarify, neatly format) your posts anyway.

( 2018-02-16 23:07:11 +0200 )edit

Sort by » oldest newest most voted

Simplify the expression to its cruicial kernel =NOT(1=1)NOT(1<>1) and vary this to see what happens: Logical functions (with exceptions) placed one aside of the other are treated as if there is an OR operator between them. The exceptions I found so far are TRUE() and FALSE() which are of a special kind anyway.

The described behaviour is clearly a bug. A quick instection of the haystick with a finger showed the bug not yet reported.

Concerning your specific question: The first formula is clearly wrong, but it works as if

=AND(OR(NOT(IFERROR(IF(SEARCH(":";$F11)>=SEARCH("U";$F11);1;0);0));
NOT(IFERROR(IF(SEARCH(":";$F11)>=SEARCH("F";$F11);1;0);0))))


was entered. AND() accepts one or more parameters, so the formula is ok. However, it will not do what you wanted.

=AND(NOT(IFERROR(IF(SEARCH(":";$F11)>=SEARCH("U";$F11);1;0);0));
NOT(IFERROR(IF(SEARCH(":";$F11)>=SEARCH("F";$F11);1;0);0)))


should be what you meant.

By the way: I needed to replace the commas in your formulas with semicolons to get my LibO accept them as intended. Please consider the advice from this thread to ease international cooperation.

As you used SEARCH() for your tests you obviously dont test case-sensitive. regarding case-imsensitivity the test seems to apply a rule like: "If the text from cell F11 is containing one or more colons neither an F or f nor an U or u is allowed left of the first colon."
Having allowed for regular expressions in functions accepting them, the folowing formula should produce what you want to achieve:

=ISERROR(SEARCH("^[^:]*(F|U).*:";F11))

more

Did you submit a bug for this NOT behaviour?

( 2018-02-17 12:30:50 +0200 )edit

I didn't yet. But I tested some cases with portable LibO V3.3, V4.4.7 and also with AOO 4.1.5.
I didn't get it exactly clear. There is another issue I came about one or two years ago: Put between ordinary comparisons in parentheses OR and AND act as Boolean operators. Probably there once was an explicit intention behind all that...

( 2018-02-17 22:21:48 +0200 )edit

I submitted tdf#115879

( 2018-02-20 13:58:59 +0200 )edit

I tried your formula and I found that I had to change a setting about regular expressions...

Your formula is more simplified and for me interesting how to work with expressions. But about you additional remark i saw that, in my case it's no problem but good to be aware of it, thank you.

In my opinion, this NOT behaviour is a bug but I think it's good to submit our expierience or is there a (better) other way?

more

( 2018-02-20 14:31:06 +0200 )edit

Oke thank you!

( 2018-02-20 15:32:37 +0200 )edit