Ask Your Question
0

No error in nested function when forgot the function separator

asked 2018-02-16 22:06:54 +0100

this post is marked as community wiki

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 flag offensive close merge delete

Comments

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 gravatar imageLupp ( 2018-02-16 23:07:11 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-02-16 23:03:50 +0100

Lupp gravatar image

updated 2018-02-16 23:45:02 +0100

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))
edit flag offensive delete link more

Comments

Did you submit a bug for this NOT behaviour?

erAck gravatar imageerAck ( 2018-02-17 12:30:50 +0100 )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.
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...

Lupp gravatar imageLupp ( 2018-02-17 22:21:48 +0100 )edit

I submitted tdf#115879

erAck gravatar imageerAck ( 2018-02-20 13:58:59 +0100 )edit
0

answered 2018-02-19 20:36:49 +0100

Hans89 gravatar image

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?

edit flag offensive delete link more

Comments

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

erAck gravatar imageerAck ( 2018-02-20 14:31:06 +0100 )edit

Oke thank you!

Hans89 gravatar imageHans89 ( 2018-02-20 15:32:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-16 22:06:54 +0100

Seen: 47 times

Last updated: Feb 19 '18