Treatment of present but empty Calc function arguments

[EvinT]

In LibreOffice calc, what are the rules for using cell function argument which is present (as defined by presence of argument separators), but empty? Is it treated as blank? As zero? As some default value for that function? What is the type of, for example, resulting IF function result? For example:

=IF(0; 1) Result is: FALSE (this one is easy, default value of optional argument is used)
=IF(0; 1;) Result is: 0 (but what is third argument here?)
=IF(1; ; 1) Result is: 0 (or second here?)
=IF(1;) Result is: 0 (or here?)
=ISTEXT(IF(1; ; 1)) Result is: FALSE
=ISNUMBER(IF(1; ; 1)) Result is: FALSE
=IF( IF(1; ; 1) = 0) Result is: TRUE
=IF( IF(1; ; 1) = “”) Result is: TRUE
=CELL(“type”; A4) Result is: v (Value, result of a formula as a number)

ISTEXT() and ISNUMBER() claim that result of IF(1; ; 1) is neither text nor number. But CELL(“type”; A4), where cell A4 contain either second, third or fourth formula, claim that content of that cell IS number. Also it is treated as equal with both empty string and zero?

What is result of IF function with such arguments? Blank value? Zero? Can we than write “real blank value” in a cell with simple IF(1;) expression?

[Lupp]

@EvinT

ISTEXT() and ISNUMBER() claim that result of IF(1; ; 1) is neither text nor number. But CELL("type"; A4), where cell A4 contain either second, third or fourth formula, claim that content of that cell IS number.

An example where the advice from my answer below applies.
An additional advice for every single minute of working with software: Expect bugs. Expect errors. Expect missing specification. Never expect flawless functioning.
And then, a bit like “Feynman’s screw rule”: In critical cases, only use formulas and tools that every user uses every day. With those, the probability that the worst bugs are fixed is significantly greater than zero.

[Villeroy]

=IF(1; ; 1) retuns 0 (evaluation of missing arg)
=ISNUMBER(0) returns 1 (True) but not =ISNUMBER(IF(1; ; 1))
IMHO, this is a bug.

[Lupp]

@Villeroy

IMHO, this is a bug.

The ISNUMBER() thing is a bug, of course.
The result 0 for the inner expression, however, is explicitly specified this way under 6.15.4 case e) in the above linked document.
It’s insane, imo, to specify such completely unneeded cases in an not at all obvious way. The reasonable specification would be to return an error value like 511 (or probably 504).
Even more insane, however, it is, to rely on such hokum by using that kind of expression. There’s no need at all thinkable, imo.
Developers may be justified for "compatibility reasons" if Excel does it this way in more than one version.

[erAck]

@Villeroy

=IF(1; ; 1) retuns 0 (evaluation of missing arg)
=ISNUMBER(0) returns 1 (True) but not =ISNUMBER(IF(1; ; 1))
IMHO, this is a bug.

Actually IF(1;;1) returns an empty path that is displayed as 0 for a final result, similar to =A1 where A1 is empty and =ISNUMBER(A1) returns FALSE as well. This way both =IF(1;;1)=0 and =IF(1;;1)="" are TRUE, where it wouldn’t for =IF(1;;1)="" if IF(1;;1) would return the number 0.

@EvinT

What is result of IF function with such arguments? Blank value? Zero?

A non-value empty path behaving like an empty cell.

@EvinT

Can we than write “real blank value” in a cell with simple IF(1;) expression?

No. A final result 0 like for a reference to an empty cell will be displayed.

[Lupp]

Which parameters of Calc functions are optional and in what way the omission is handled can differ depending on the function. The general rules and the special cases both are specified here:
Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format.
Offline version as pdf file for download:
https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.pdf

There are some cases where the linked specifications allow for differences between “implementations” (LibreOffice Calc one of them). In these cases you should find described the behaviouir of Calc in the help.

Concerning your special interest in omission of arguments: Generally it should be good advice to simply not omit arguments if not absolutely sure that the result will be as wanted. Avoid specifically every doubtable case where you expected an error-result, but didn’t get one. There may be a bug or a dangerous case of unspecified behaviour.

As far as I know there are only two functions where -unfortunately- the omission of an argument is unavoidable in some use-cases: INDEX() and REGEX(). Study these function to the needed detail.

(Concerning user defined functions see also tdf#102381.)