Exploring for answers about Empty Cells (IsBlank, CountBlank), I ran across this old question (https://ask.libreoffice.org/t/54718) which was create Jul 2020 and closed May 2021. I can’t reopen it, so I will add this topic. There is (now) a better answer to the question (although he got an answer that did what he wanted to do).
Using a pair of curly braces, {}, returns a value of an empty cell. =IF(G3<>0,G3/J2,{}) will result in either the quotient or an ‘empty’ cell, even though there is a formula in the cell. I have not fully explore its behavior with all possible combinations of references and functions (IsBlank, CountBlank, IsFormula, etc), but for (most) everything I have used, it works as an empty cell when the formula returns the ‘{}’ result.
I never saw the use of empty {} as Argument, but it seems to work similar to:
=IF( G3<>0 ; G3/J2 ; "")
Except it will give FALSE for ISTEXT (but will continue to give TRUE for ISREF and ISFORMULA). Also, COUNTBLANK treats it as a blank - even though ISBLANK keeps giving FALSE.
@LaVerne it would be better to re-format it as a question, and then provide the solution in an answer, which you would mark as an accepted solution. That way, the question will appear in searches as having a solution.
It depends… only if directly evaluated through a non-reference:
A1: =IF(0;1;{})
A2: =COUNTBLANK(A1) => 0
A3: =COUNTBLANK({}) => 1
A4: =COUNTBLANK(IF(0;1;{})) => 1
Cause probably is that in A2 resolving the reference the formula cell with the empty array at that position hasn’t a string content so it also can’t be an empty string, while A3 and A4 result in an actual empty array being passed though empty arrays have always a dimension 1x1 with no element (resulting in either empty or 0 or “” when accessed), so determining blanks there are no countable elements but a size, of which the difference is the blanks… probably the emptiness should be considered and A3 and A4 should return 0 instead. Not sure if A2 should return 1 instead…
That Excel #CALC! error is about computed empty spill result sets though.
However, I’m not sure that accepting an empty inline array like ={} is proper either, looks rather like an oversight, even if it allows to take advantage of a side effect in the OP’s context.
@erAck , I’m also confused by the fact that when I enter
formulas
={1}
and pressing Enter the array formula appears.
Previously, I thought that Ctrl+Shift+Enter is required to enter an array formula?
It’s a feature since 7.4; if it’s detected that the result is an array then an array mode formula is created when entering it. See LibreOffice 7.4 Community: Release Notes - The Document Foundation Wiki “Automatic cell matrix expansion …”.
Thanks, I missed that.