How can I assign empty to a cell - reprise

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.

2 Likes

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.

1 Like

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…

2 Likes

By the way. Excel rejects the formula in A1.

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 …”.

1 Like

Thanks, I missed that.