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

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)

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

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

1 Like