Trouble with conflicting IsBlank() and CountBlank() results

[Rich]

I'm using the following formula in cell c1:
=if(isblank(a1),"",a1)
to "copy" the a1 cell value to cell c1 without displaying a zero if a1 is
blank.

The trouble I'm having is if cell a1 is blank, thus making the value of c1 =
""....
=isblank(c1) returns false (the cell is not blank), but...
=countblank(c1) returns 1 (the cell is blank)

Why does CountBlank consider a cell with the value of "" as blank, but
IsBlank does not??

I found this apparent discrepany in results because I actually want to use
CountA(c1:c4) to count non-blank cells, but it's count cells with a value of
"" as a non-blank cell.

I know I can "work around" this by using 4-countblank(c1:c4) to find the
number of non-blank cells, but I like the direct route better.

Is there a better way to copy a blank value from a1 to c1??

 

[JLGWhiz]

Not sure I follow you:
=IF(ISBLANK(A1),"",A1)

The above formula will put an empty string in the formula cell, if
ISBLANK(A1) is true, else it will return the value of A1 othere than blank
or empty string. So if you are getting "" in C1, you are getting the
correct return.

I don't work with formulas a lot but it seems like I read somewhere that
Excel had some quirks with the IsBlank in respect to the empth string ("").
I know that in VBA IsEmpty and the empty string and zero have distinctive
differences, so care has to be taken in what one tests for.

Testing for values greater than zero can sometimes work better than testing
for blanks.

 

[JLGWhiz]

Take a look at this, it might help to explain the problem.

The Len(a1) = 0 might be the work-around.

 

[JLGWhiz]

Forgot to paste the link.

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20978027.html

 

[Joe User]

I'm using the following formula in cell c1:
=if(isblank(a1),"",a1)
to "copy" the a1 cell value to cell c1 without displaying a zero
if a1 is blank.

You probably want to use the following formula:

=IF(A1="","",A1)

Why does CountBlank consider a cell with the value of ""
as blank, but IsBlank does not?

Unix developers have a saying: "Never ask ``why?``." It is what it is.

Your confusion is understandable because of the ambiguous meaning of the
word "blank" in the vernacular as well as it is used in Excel.

The simple answer to your question ("why?") is: don't get hung up on the
word "blank"; always RTFM.

The Help page for IS functions says that ISBLANK returns TRUE if its
argument "refers to an empty cell". I don't know if "empty cell" is ever
formally defined; it means: a cell that has no constant and no formula. So
by definition, a formula cannot return an "empty cell"; it can only return a
value that __appears__ blank.

In contrast, the Help page for COUNTBLANK says that in addition to counting
"empty cells", ``cells with formulas that return "" (empty text) are also
counted``. Why? Because that's the way it is.

(IMHO, "" should be call a null string or null text, not "empty text".)

I found this apparent discrepany in results because I actually want to use
CountA(c1:c4) to count non-blank cells, but it's count cells with a value
of
"" as a non-blank cell.
[....]
Is there a better way to copy a blank value from a1 to c1?

I'm confused. On the one hand, you say you want to __count__ "non-blank"
cells (i.e. cells that do not appear blank). On the other hand, you ask for
a better way to __copy__ a "blank value".

Those two very different actions with very different solutions.

As to the latter (copy), see the formula above. As to the former
(counting), consider:

=SUMPRODUCT(--(C1:C4<>""))

Note: A cell containing a "blank string" -- that is, text composed of one
or more spaces, which is what you enter when you press the Space bar -- is
counted as non-blank, even though it also appears blank. If you also want
to exclude such cells from your count of "non-blank" cells, consider:

=SUMPRODUCT(--(TRIM(C1:C4)<>""))


----- original message -----