Trapping and Counting Empty Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2267007%22%20slang%3D%22en-US%22%3ETrapping%20and%20Counting%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267007%22%20slang%3D%22en-US%22%3E%3CP%3ERudest%20comments%20on%20the%20following%2C%20please.%20But%20note%20that%20at%20the%20end%20I%20propose%20two%20new%20functions%20to%20help%20us%20out%20of%20the%20confusion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMicrosoft's%20various%20help%20resources%20have%20a%20problem%20with%20the%20difference%20between%20%22Blank%22%20and%20%22Empty%22%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20start%20by%20defining%3A%3C%2FP%3E%3CP%3E%22Empty%20cells%22%20are%20cells%20having%20nothing%20in%20them%20at%20all.%3C%2FP%3E%3CP%3E%22Blank%20cells%22%20however%2C%20could%20be%20defined%20as%20including%20ones%20that%20contain%20those%20'%2C%20%22%2C%20and%20%5E%20text%20alignment%20indicators%20often%20left%20after%20cleaning%20cells%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EISBLANK%20returns%20TRUE%20if%20a%20cell%20is%20truly%20empty%20but%20FALSE%20if%20it%20contains%20blanks.%3C%2FP%3E%3CP%3ECOUNTBLANK%2C%20one%20would%20think%2C%20should%20return%20the%20number%20of%20cells%20that%20ISBLANK%20returns%20TRUE%20for.%3C%2FP%3E%3CP%3EBut%20it%20doesn't!%20It%20counts%20both%20empty%20and%20blank%20cells%20as%20per%20our%20definition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo!%20Ignoring%20the%20misleading%20name%2C%20we%20can%20use%20ISBLANK%20to%20find%20empty%20cells!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20count%20empty%20cells%20the%20only%20way%20I%20know%20is%20to%20use%20coercion%20of%20the%20returns%20of%20ISBLANK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20ISBLANK%20checks%20in%20C11%3AC19%20we%20can%20count%20the%20empty%20cells%20using%3A%3C%2FP%3E%3CP%3E(e.g.)%20%3DSUM(--(C11%3AC19))%3C%2FP%3E%3CP%3EAlthough%20I%20prefer%26nbsp%3B%3DSUM((C11%3AC19)*1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVarious%20descriptions%20are%20misleading%3A%3C%2FP%3E%3CP%3EThe%20User%20Interface%20description%20for%20COUNTBLANK%20misleads%20us%2C%20%22Returns%20the%20number%20of%20empty%20cells%20in%20a%20specified%20range%20of%20cells%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20All%20Functions%20listing%20for%20COUNTBLANK%20is%20more%20accurate%20as%2C%20%22%3CSPAN%3ECounts%20the%20number%20of%20blank%20cells%20within%20a%20range%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20User%20Interface%20description%20for%20ISBLANK%20and%20the%20Help%20File%20are%20correct%20in%20referring%20to%20it%20returning%20TRUE%20or%20FALSE%20for%20%22empty%22%20cells.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20Description%20of%20ISBLANK%20in%20the%20All%20files%20listing%20is%20wrong%20in%20saying%2C%20%22Returns%20TRUE%20if%20the%20value%20is%20blank%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20idea%20of%20heaven%20would%20be%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ETwo%20new%20functions%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EISEMPTY%20Returns%20TRUE%20if%20the%20cell%20contains%20nothing.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECOUNTEMPTY%20Returns%20the%20number%20of%20empty%20cells.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EObviously%2C%20My%20ISEMPTY%20would%20have%20exactly%20the%20same%20algorithm%20as%20ISBLANK%20-%20it's%20just%20more%20appropriately%20named.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20COUNTEMPTY%20on%20the%20other%20hand%20is%20really%20new%20and%20returns%20the%20number%20of%20empty%20cells.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20case%20you%20think%20this%20is%20semantics%2C%20you%20should%20be%20aware%20that%20it%20impacts%20upon%20the%20NPV%20and%20IRR%20functions%20which%20can%20be%20wrong%20if%20the%20empty%20and%20blank%20cells%20are%20not%20handled%20properly.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBut%20then%20it's%20Monday%20morning%20here%20in%20Australia%2C%20and%20I%20start%20the%20week%20hopefully.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267007%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2268508%22%20slang%3D%22en-US%22%3ERe%3A%20Trapping%20and%20Counting%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F855269%22%20target%3D%22_blank%22%3E%40Norman_Harker%3C%2FA%3E%26nbsp%3B%2C%20that's%20a%20long%20story%20discussion.%20Blank%20as%20value%20is%20partially%20supported%20in%20XMATCH%20and%20XLOOKUP%2C%20e.g.%20XMATCH(%2C%20one%2C%20two).%20I%20believe%20ISEMPTY()%20or%20like%20is%20coming%20with%20future%20versions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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.

@Sergei Baklan 

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.

@Norman_Harker 

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.

@Sergei Baklan Two problems you've referred to Sergei. We do have a fully functional function that returns TRUE if a cell is empty. It's just called ISBLANK when a blank cell is not an empty cell. We don't have a function that counts empty cells. My work demonstrated with crystal clarity both of those points. We DO need a function that counts empty cells and that avoids having to use the technique I demonstrated of coercing the returns of a range or ranges of ISBLANK returns.

 

More important the Developers need to correct the change that they made to the IF function that allows a value_if_true argument to be optional. In my opinion as a specialist in financial areas that has created a risk of corrupting returns from NPV and IRR functions and it probably corrupts returns of other areas of analysis. The worse thing, is that it is extremely difficult to catch and treat separately the 'optional default 0' from the 'required by user; return or 0. What advantage was secured by changing that value_if_true argument? I can't see any advantage at all.

 

Meanwhile, this and the numerous / almost numberless mistakes and omissions that I point out in an attempt to improve user experience with a fantastic spreadsheet program get totally ignored by Microsoft.

 

Thanks anyway, Sergei