Blank vs empty cells

  • Thread starter Thread starter [Mr.] Lynn Kurtz
  • Start date Start date
M

[Mr.] Lynn Kurtz

Suppose cell A1 is empty, B1 has =isblank(A1), and C1 has
=countblank(A1).

Then B1 evaluates to true and C1 evaluates to 1. OK so far.

Now change A1 to the formula = "", the empty string. Now B1 shows
false but C1 shows true. This seems a bit inconsistent.

Now put a 3 in A2 and set A3 = A1 + A2. This gives a #value error when
A1 has the empty string. But if you change A3 to = sum(A1:A2) it
treats the empty string as a zero and gives A3 evaluating to 3, an
inconsistency between the sum function and explicit addition.

If you delete the contents of A1 with the delete key so the cell is
actually empty, everything becomes consistent. Apparently a blank cell
and an empty cell aren't the same thing and aren't treated the same.

So in a sqreadsheet I might have a cell with a formula like this:

=if(qty > 0, qty*cost, "")

to display blanks instead of meaningless zeroes for things that aren't
ordered. But to avoid the #value error in a formula that references
this cell, instead of having the empty string in that if statement I
wonder how to get something with this effect:

=if(qty > 0, qty*cost, set cell empty as if the del key had been
pressed)

Comments anyone?

--Lynn
 
This seems a bit inconsistent.

At the very least, it's confusing! ISBLANK should have been named ISEMPTY.
instead of having the empty string in that if statement
I wonder how to get something with this effect:
=if(qty > 0, qty*cost, set cell empty as if the del
key had been pressed)
Click to expand...

Can't be done. As long as the cell contains a formula the cell is not empty.

When dealing with formula blanks in math operations, instead of doing this:

=A1+A2

Use the SUM function. It will ignore the formula blanks.

=SUM(A1:A2)

--
Biff
Microsoft Excel MVP


[Mr.] Lynn Kurtz said:
Suppose cell A1 is empty, B1 has =isblank(A1), and C1 has
=countblank(A1).

Then B1 evaluates to true and C1 evaluates to 1. OK so far.

Now change A1 to the formula = "", the empty string. Now B1 shows
false but C1 shows true. This seems a bit inconsistent.

Now put a 3 in A2 and set A3 = A1 + A2. This gives a #value error when
A1 has the empty string. But if you change A3 to = sum(A1:A2) it
treats the empty string as a zero and gives A3 evaluating to 3, an
inconsistency between the sum function and explicit addition.

If you delete the contents of A1 with the delete key so the cell is
actually empty, everything becomes consistent. Apparently a blank cell
and an empty cell aren't the same thing and aren't treated the same.

So in a sqreadsheet I might have a cell with a formula like this:

=if(qty > 0, qty*cost, "")

to display blanks instead of meaningless zeroes for things that aren't
ordered. But to avoid the #value error in a formula that references
this cell, instead of having the empty string in that if statement I
wonder how to get something with this effect:

=if(qty > 0, qty*cost, set cell empty as if the del key had been
pressed)

Comments anyone?

--Lynn
Click to expand...
 
What you say is true. ISBLANK is true if and only if the cell <contains>
nothing.
While COUNTBLANK counts cells that are <displaying> nothing
="" has a formula (ISBLANK = false) , but displays nothing (COUNTBLANK = 1)
=" " has a formula (ISBLANK = false) , but displays a space (COUNTBLANK = 0)

For your problem, why not use the Tool | Option | View to have zeros not
displayed
This is option is at the worksheet level

It is well known that SUM() ignores text; as does PRODUCT
This 'inconsistency' can be of great value to some users
best wishes
 
Hi,

Let me add a little more to make you more unhappy:
=CELL("Type",A1) returns B if the cell is blank and L if it contains a
formula evaluating to ""
=COUNTIF(A1,) returns 0 if the cell contain "" , but 1 if it is empty
=COUNTIF(A1,"") returns 1 if the cell conatins "", and 1 if it is empty

Isn't Excel fun.

Cheers,
Shane Devenshire
 
And one more:

=CELL("contents",A1) returns 0 if A1 is empty, but it returns nothing if
A1 evaluates to "".

This is all consistant, I suppose.

Cheers,
Shane Devenshire
 
Back
Top