iferror(1/0) in Spreadsheets

A common desire in Spreadsheets is to actually return a blank cell depending on certain conditions.  There are a few ways to do this, some that actually look like blank cells and aren't and some that are truly blank.

Consider this Forum Question.  Cherub96 wants the cell to actually be blank if his source cells for his calculations are blank.  Yogi's answer is perfect and is recommended.  BenFDC also chimes in with a similar answer that is also recommended.  Both answers give the exact same result.


The most important aspect of this post is that it is possible to return a truly blank cell in Google Spreadsheets!


The way to do this using the iferror trick.  This trick is done by wrapping an error expression in iferror and omitting the second argument.  An error expression is any expression that returns an error such as 1/0, sqrt(-X), NA() etc.  =iferror(1/0) has become common usage and is advantageous because you can easily create an array of blank cells.


However, there remains a bug whereby if, say, A1 is formatted as Plain Text, and text is entered in that cell, then =ISBLANK( A1 ) will return FALSE (as expected), but if the entry is then deleted form A1, then =ISBLANK( A1 ) still returns FALSE.  The cell is behaving as though it is housing a zero-length string, and presents another reason why we might generally resort to the LEN( ) test.


Notice, if cell A1 is formatted as Plain Text, a Text entry is made in the cell

=isblank(A1) returns FALSE, =Len(A1) returns 0 as expected

When Text entry in cell A1 is deleted

=isblank(A1) returns FALSE, =Len(A1) returns 0  not as expected

However, if A1 is reformatted as Normal and 

when entry in cell A1 is deleted

=isblank(A1) returns TRUE, =Len(A1) returns 0 as expected

Therefore, it appears the bug has to do with formatting a cell as Plain Text.

Happy blank cell making!

Thanks to Adam, André, and Yogi for their help with contributions on this post.

Cheers!