"" is an empty cell?

[Paul Smith]

Dear All

If I insert

=""

in cell A1 and

=2

in cell B1, then when I insert

=A1*B1

in cell C1, I do not get 0. Does not "" correspond to an empty cell?

Thanks in advance,

Paul

 

[Alan]

No it doesn't. "" in a cell is a null string which means it isn't empty but
contains "". You're formula =A1*B1 is multiplying "" by 2 which is
effectively multiplying a text value by 2, so it will return #VALUE!
Regards,
Alan

 

[joeu2004]

If I insert
=""
in cell A1 and
=2
in cell B1, then when I insert
=A1*B1
in cell C1, I do not get 0. Does not "" correspond to an empty cell?

No; and ISBLANK() returns false for such cells. In addition to ="", I
tried entering a blank space, ' (apostrophe followed by nothing else),
=if(true,"") and =" ", and none works in a simple arithmetic expression
(e.g. 1*A1). You could use something like:

=B1*if(isnumber(A1), A1, 0)

However, I discovered that SUM(A1) returns zero in all cases (at least
in Office Excel 2003). So you could try:

=B1*sum(A1)

[Arrgghh! That might appear to give some justification for all the
nonsensical uses of SUM(), e.g. SUM(A1+B1). But in fact, only SUM(A1)
results in zero in all of the above cases, not for example SUM(1*A1).]

 

[Paul Smith]

If I insert
=""
in cell A1 and
=2
in cell B1, then when I insert
=A1*B1
in cell C1, I do not get 0. Does not "" correspond to an empty cell?

No; and ISBLANK() returns false for such cells. In addition to ="", I
tried entering a blank space, ' (apostrophe followed by nothing else),
=if(true,"") and =" ", and none works in a simple arithmetic expression
(e.g. 1*A1). You could use something like:

=B1*if(isnumber(A1), A1, 0)

However, I discovered that SUM(A1) returns zero in all cases (at least
in Office Excel 2003). So you could try:

=B1*sum(A1)

[Arrgghh! That might appear to give some justification for all the
nonsensical uses of SUM(), e.g. SUM(A1+B1). But in fact, only SUM(A1)
results in zero in all of the above cases, not for example SUM(1*A1).]

Thanks, but what is more surprising is that OpenOffice and Gnumeric
both treat cells with "" as being filled with 0 in arithmetic
calculations. So, how can one blank a cell with through a formula?

Paul

 

[Dave Peterson]

If you wanted to treat text as 0, you could use:

=n(a1)*b1

to get 0.

 

[JE McGimpsey]

You can't. If a cell contains a formula, it's not blank, it contains the
result of the formula.

I'm not sure what you're trying to accomplish by putting ="" in a cell
that you want to multiply by another cell (as opposed to, say, 0), but
you've been given a couple of workarounds. There are others, too, that
can be useful, depending on your needs.

And of course, if you just want that cell to *appear* blank if it has a
zero value, you can use a custom format:

General;General;;@