Forum Discussion
Trapping and Counting Empty Cells
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.
SergeiBaklan 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