Forum Discussion
Trapping and Counting Empty Cells
Rudest comments on the following, please. But note that at the end I propose two new functions to help us out of the confusion.
Microsoft's various help resources have a problem with the difference between "Blank" and "Empty" cells.
Let's start by defining:
"Empty cells" are cells having nothing in them at all.
"Blank cells" however, could be defined as including ones that contain those ', ", and ^ text alignment indicators often left after cleaning cells
ISBLANK returns TRUE if a cell is truly empty but FALSE if it contains blanks.
COUNTBLANK, one would think, should return the number of cells that ISBLANK returns TRUE for.
But it doesn't! It counts both empty and blank cells as per our definition.
So! Ignoring the misleading name, we can use ISBLANK to find empty cells!
To count empty cells the only way I know is to use coercion of the returns of ISBLANK.
With ISBLANK checks in C11:C19 we can count the empty cells using:
(e.g.) =SUM(--(C11:C19))
Although I prefer =SUM((C11:C19)*1)
Various descriptions are misleading:
The User Interface description for COUNTBLANK misleads us, "Returns the number of empty cells in a specified range of cells"
The All Functions listing for COUNTBLANK is more accurate as, "Counts the number of blank cells within a range"
The User Interface description for ISBLANK and the Help File are correct in referring to it returning TRUE or FALSE for "empty" cells.
The Description of ISBLANK in the All files listing is wrong in saying, "Returns TRUE if the value is blank"
My idea of heaven would be:
Two new functions:
ISEMPTY Returns TRUE if the cell contains nothing.
COUNTEMPTY Returns the number of empty cells.
Obviously, My ISEMPTY would have exactly the same algorithm as ISBLANK - it's just more appropriately named.
My COUNTEMPTY on the other hand is really new and returns the number of empty cells.
In case you think this is semantics, you should be aware that it impacts upon the NPV and IRR functions which can be wrong if the empty and blank cells are not handled properly.
But then it's Monday morning here in Australia, and I start the week hopefully.
4 Replies
Norman_Harker , that's a long story discussion. Blank as value is partially supported in XMATCH and XLOOKUP, e.g. XMATCH(, one, two). I believe ISEMPTY() or like is coming with future versions.
Thanks Sergei! ISEMPTY would be useful but hardly rocket science for developers because it's only a case of giving the right name to ISBLANK.
What would be more useful is COUNTEMPTY for which there is no current function and the only way (I think) of returning the number of empty cells is =SUM(--(C11:C19)) or equivalent where C11:C19 is a range containing ISBLANK returns 'looking at B11:B19.
It's easy to program a UDF to allow (e.g.) =MYCOUNTEMPY(B11:B19) but wouldn't it be better for Developers to give us this very commonly needed function.
The point I make is that the impact on NPV and IRR functions of empty and blank cells in the range is such that this is an important pre-calculation requirement for robust analysis.
Something like this. Full functional blank as value will be quite usefull, not only in checking if the cell is actually blank, but in returning actual blanks. I mean =IF(1,) shall return blank value, not zero.
