No error in nested function when forgot the function separator

[Hans89]

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

[Lupp]

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.

[Lupp]

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.

An additional remark:
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))

[erAck]

Did you submit a bug for this NOT behaviour?

[Lupp]

I didn’t yet. But I tested some cases with portable LibO V3.3, V4.4.7 and also with AOO 4.1.5.
It’s basically the same. Nobody worried about this for decades now.
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…

[erAck]

I submitted tdf#115879

[Hans89]

Thank you for your answer Lupp!

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?

[erAck]

Please don’t use the Answer field to comment on an answer, use add a comment instead. Thanks.

[Hans89]

Oke thank you!