# Treatment of present but empty Calc function arguments

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?

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.

=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.

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.

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.

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

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

2 Likes

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.