ISBLANK COUNTBLANK COUNTA discrepancy

[classtrainingqld]

This is a problem that has reoccured....

I have a client that has sent me a speadsheet where a COUNTA function
appears not to be working...

I copied one of the (many) problem cells being counted to cell A1 in a
new workbook.

in cell B1 I entered ISBLANK(A1) this returned false - so this
suggests it is not blank

in cell C1 I entered COUNTBLANK(A1) this returned 1 - so this suggests
it is blank

in cell D1 I entered COUNTA(A1) this returned 1 - so this suggests it
is has something in it

in cell E1 I entered LEN(A1) this returned 0 - so this suggests it is
empty

I then decided to check it against a true blank cell..

in cell B2 I entered ISBLANK(A2) this returned true - which is OK
because I haven't entered or touched A2

in cell B3 I entered EXACT(A1,A2) this returned true - this suggests
A1 is also blank.

bizarre in the extreme... has anyone ever seen this before or have any
suggestions on what could be in this cell.

If I edit the cell and press Enter (without any changes) it is then
treated as a true blank cell.

Thanks in advance

Regards

Steve Bayliss
Class Training Qld
Australia

 

[Harlan Grove]

...
...

I have a client that has sent me a speadsheet where a COUNTA function
appears not to be working...

I copied one of the (many) problem cells being counted to cell A1 in a
new workbook.

in cell B1 I entered ISBLANK(A1) this returned false - so this
suggests it is not blank

in cell C1 I entered COUNTBLANK(A1) this returned 1 - so this suggests
it is blank

in cell D1 I entered COUNTA(A1) this returned 1 - so this suggests it
is has something in it

in cell E1 I entered LEN(A1) this returned 0 - so this suggests it is
empty

This *is* a discrepancy, but it's one that it seems Microsoft intended.

First, this discrepancy involves cells containing zero length strings, i.e.,
cells containing formulas that evaluate to the same result as the formula ="",
cells in which a single apostrophe only has been entered, or cells produced by
copying cells as just described and pasting them as values. In the last case,
cells may appear blank but Excel would still consider them to contain text.

COUNTA(such_a_cell) will return 1 because the cell isn't blank using the strict
definition of 'blank' in the sense that ISBLANK(such_a_cell) would return FALSE.

COUNTBLANK(such_a_cell) will also return 1 because COUNTBLANK includes both
truly blank cells (in the ISBLANK returns TRUE sense) and cells evaluating to
"". This is a royal PITA, but this is just how Excel works. If you want to count
truly blank cells, though only in single area ranges, use COUNTIF(x,"="). If you
want to count cells evaluating to "" but not truly blank cells, though again
only in single area ranges, use COUNTIF(x,"<*"). Use COUNTBLANK only when you
want to count both truly blank cells and cells evaluating to "".

The *KEY* point is that Excel obliges you to distinguish between 'blank' cells
(in the sense that ISBLANK returns true, which in turn means the cell's .Value
property is Empty [getting really technical]) and 'empty' cells, which means
either truly blank cells or cells evaluating to "" (aka zero-length strings).

I then decided to check it against a true blank cell..

in cell B2 I entered ISBLANK(A2) this returned true - which is OK
because I haven't entered or touched A2

in cell B3 I entered EXACT(A1,A2) this returned true - this suggests
A1 is also blank.

Incorrect!

Truly blank cells have .Value properties that are 'Empty' in the VBA sense (just
to confuse things). Excel can't represent VBA's Empty value, so it converts such
cells to 0 in numeric contexts and "" in text contexts. If you were to enter the
formulas

E1:
=A1+0

E2:
=A2+0

F1:
=">"&A1&"<"

F2:
=">"&A2&"<"

you'd find that E1 returns #VALUE!, E2 0, and F1 and F1 "><".

bizarre in the extreme... has anyone ever seen this before or have any
suggestions on what could be in this cell.

As you may have inferred already, this is WELL KNOWN, LONG ESTABLISHED Excel
functionality. You learn it and get used to it.

If I edit the cell and press Enter (without any changes) it is then
treated as a true blank cell.

Yup. It appears such cells were originally formulas evaluating to "" which were
copied then pasted as values (Edit > Paste Special > Values). Such cells have
Formula *and* .PrefixCharacter properties both equal to "", but .Value
properties equal to "" rather than Empty. The [F2], [Enter] sequence makes the
cell truly blank because *entering* no formula effectively clears the cell. The
only way to create these almost blank cells is pasting into them, which is *not*
handled the same as entering such cells.

Such cells are among the nastiest critters Excel foists upon unwary spreadsheet
users. You just have to learn the defensive formula techniques necessary to deal
with them. See the COUNTIF calls I gave above.