"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())
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.
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.
Created attachment 124773 [details] Screenshot without error I can't see the error, perhaps I miss something.
the problem is not the function itself, it is the documentation!
Sorry I can't understand what it's exactly bad in the help, sure my non English.
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!
Let's set to NEW.
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.
It is also strange because =IF(A1>5, , "too small") returns 0, the equivalent of FALSE, if A1>5 is true.
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
Or rather canonical URL https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#IF
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