Download it now!
Bug 99613 - optional Paramaters for IF is optional
Summary: optional Paramaters for IF is optional
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: sdc.blanco
URL:
Whiteboard: target:7.2.0
Keywords:
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
 
Reported: 2016-05-01 20:01 UTC by Dennis Roczek
Modified: 2020-12-17 14:50 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot without error (26.63 KB, image/png)
2016-05-01 22:00 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Roczek 2016-05-01 20:01:53 UTC
"In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone."
Quoted from IF function at
https://help.libreoffice.org/Calc/Logical_Functions#IF
http://vm173.documentfoundation.org/text/scalc/01/04060105.xhp

This is simply untrue in the case of IF, try it yourself with:
=IF("a"="b";;)

(If that makes sense is totally different, but it works, same useful as =FALSE())
Comment 1 m.a.riosv 2016-05-01 21:39:14 UTC
Hi @Dennis, it returns zero, it's not right?

The only thing could be that the cell is not auto-formatted as Boolean, but there is not calculation in the parameters to forces it, and an IF can returns any type of result not necessarily a Boolean.

Suppressing both parameters =IF("a"="b") then returns FALSE, inheriting from the comparison inside the function.
Comment 2 Dennis Roczek 2016-05-01 21:48:52 UTC
Hi miguel,
well it doesn't matter if it return 0 or FALSE (depending on the ; or the formatting of the cell): the complete sentence is wrong: the sentence imply that an ERR:501 appear similar to =ACOS()

so, simplest "solution" would be to remove the complete sentence as it is untrue.
Comment 3 m.a.riosv 2016-05-01 22:00:57 UTC
Created attachment 124773 [details]
Screenshot without error

I can't see the error, perhaps I miss something.
Comment 4 Dennis Roczek 2016-05-01 22:03:35 UTC
the problem is not the function itself, it is the documentation!
Comment 5 m.a.riosv 2016-05-01 22:10:00 UTC
Sorry I can't understand what it's exactly bad in the help, sure my non English.
Comment 6 Dennis Roczek 2016-05-02 12:18:24 UTC
The paragraph:

"In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone."

is wrong. Parameter 2 and 3 can omitted - this paragraph indicates something different!
Comment 7 Buovjaga 2016-05-02 14:58:55 UTC
Let's set to NEW.
Comment 8 Travis Stewart 2020-10-06 22:37:37 UTC
I am not sure if I understand the original bug correctly, but I think there is an actual issue with the documentation or the IF function. The Logical Functions help page (https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060105.html?&DbPAR=WRITER&System=UNIX) talks about optional parameters using a hypothetical 4 parameter function.

"In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone."

By applying this example to the IF function, where parameters 2 and 3 are optional, you can use it with all 3 parameters included or with parameters 1 and 2 only, but you can't use it with parameters 1 and 3 with 2 left out.

This is the expected behaviour:
=IF(A1>5,100,"too small") is valid. If A1 <= 5 it returns "too small" otherwise it returns 100.
=IF(A1>5,100) is also valid. It returns 100 if A1 > 5 is TRUE, and FALSE otherwise. Parameter 3 is left out, but that's okay because no parameter follows it.
=IF(A1>5, , "too small") is invalid because you can only leave out optional parameters if no parameters follow. But in this case, the optional parameter 2 is left out and followed by a parameter. You'd expect an error, it shouldn't work based on the statement in the documentation.

But you can leave out parameter 2 and it still does something, even though the documentation says it can't be left out.
=IF(A1>5, , "too small"), A1 > 5 it returns 0, if it is <= 5, it returns "too small"

If the documentation is correct, it should not interpret an empty parameter as a 0 and use that as the ThenValue. If the documentation is wrong, then how it handles empty parameters needs to be discussed in the documentation.
Comment 9 Travis Stewart 2020-10-06 22:56:35 UTC
It is also strange because =IF(A1>5, , "too small") returns 0, the equivalent of FALSE, if A1>5 is true.
Comment 10 Eike Rathke 2020-10-07 11:54:49 UTC
Note that in IF(A1;;"something") the second argument *is* given, but given as empty argument. An empty argument usually evaluates to 0.

Do not confuse absent (trailing optional argument omitted) and empty null arguments.

See also ODFF 6.15.4 IF
https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#IF
Comment 12 Commit Notification 2020-12-17 14:41:42 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/9d505510f15289b1393c2b2c654b23f03790c250

tdf#99613  clarify empty parameters in IF function in Calc