Neither ISTEXT() nor ISNUMBER() nor ISERROR(), but ISFORMULA(): Why possible?

Lupp · May 14, 2025, 12:43pm

There were questions like “How to get a blank cell by formula?” and an old one of them has about 20 replies and the valid and accepted answer “impossible” given by @Regina.
On the other hand there are formulas like {=[]} returning FALSE for all the given question-formulas except ISFORMULA().
Neither inspecting cell properties by user code nor studying the pdf-specifications for ISTEXT() and ISNUMBER() solved the puzzle for me.
Is the described situation

a bug?
intended but undocumented behaviour?
misunderstood by myself?

Demonstrating sheet:
disaskOwnQu122055_VirtuallyBlankCells.ods (20.9 KB)

Villeroy · May 14, 2025, 2:20pm

An empty array is neither one of number, text nor error.
Surprisingly, it is a ref =ISREF(A2) returns TRUE.

Lupp · May 14, 2025, 3:31pm

=ISTEXT(A2) returns the resulttype of the cell referenced by the parameter A2.
=ISREF(A2) returns the type of the parameter.
=ISREF(INDIRECT("A1")) returns TRUE because the INDEX() function is specified to return a reference (or an error).
The (result or content) type of cell A1 is text in the example.

It’s not easy to explain such ticklish differences in a short sentence:

6.13.24 ISREF()
Summary: Return TRUE if the parameter is of type reference, else return FALSE.

Paraphrasing Magritte: Ceci n’est pas une pipe.

fpy · May 14, 2025, 7:26pm

actually quoting.

but more generally, don’t expect just perfect intuitive systems everywhere
Gödel's incompleteness theorems - Wikipedia

Lupp · May 14, 2025, 8:48pm

Well,
A statemenmt like …
0 0 0
Some functions, namely OFFSET() and INDIRECT() are specified to return references.
If a formula or subexpression returns a result being a reference to a cell, or to a surrounding expression expecting ‘ordinary values’, automatic de-referencing will take place. However, if a parameter of a function is specified to accept references as arguments, de-referencing can be omitted and an explicit evaluation of the reference can be applied. CELL() with a lot of supported first text arguments and a second reference argument, and ISREF() are such functions*.
000
… would not be attractive, and probably not read or not understood.
I tried to trust in associations in this case.
This may have been a mistake.