Hi,

I wonder if anyone can help me with a seemingly straightforward, but
tricky, Excel "feature".

It appears that Excel treats differently a blank cell and a cell
containing a formula that returns blank. E.g., try the following
simple case:

A1: leave blank
B1: enter formula: =""
A2: enter formula: =A1+1
B2: enter formula: =B1+1

Result of A2 = 1
Result of B2 = #VALUE!

When trying to sum such cells, the blank cell is happily treated as a
zero, whereas the one with a formula in it causes an error.

(A bit more detail - I am pulling numbers from one sheet to another.
But when a number is missing on the first sheet I want a blank to be
inserted in the second, rather than a zero. But this messes up
formulae on the pulled through values)

Any ideas how to get round this?

Thanks,

Matthew

Use

=SUM(1,B1)

or

=N(B1)+1



--
Regards,

Peo Sjoblom


wrote in message
ups.com...
Hi,

I wonder if anyone can help me with a seemingly straightforward, but
tricky, Excel "feature".

It appears that Excel treats differently a blank cell and a cell
containing a formula that returns blank. E.g., try the following
simple case:

A1: leave blank
B1: enter formula: =""
A2: enter formula: =A1+1
B2: enter formula: =B1+1

Result of A2 = 1
Result of B2 = #VALUE!

When trying to sum such cells, the blank cell is happily treated as a
zero, whereas the one with a formula in it causes an error.

(A bit more detail - I am pulling numbers from one sheet to another.
But when a number is missing on the first sheet I want a blank to be
inserted in the second, rather than a zero. But this messes up
formulae on the pulled through values)

Any ideas how to get round this?

Thanks,

Matthew

On 18 May, 18:21, "Peo Sjoblom" wrote:
Use

=SUM(1,B1)

or

=N(B1)+1

Thanks. I'm not entirely sure it helps though - I only posted the
simplest case I could think of that demonstrates the problem, rather
than what I'm actually doing.

I can come up with other workarounds, but what I really want is a way
for a formula to enter a real blank into the cell, rather than an
empty string.

Matthew

No, it is not possible to get a formula to return an empty string in Excl.


--
Regards,

Peo Sjoblom



wrote in message
oups.com...
On 18 May, 18:21, "Peo Sjoblom" wrote:
Use

=SUM(1,B1)

or

=N(B1)+1

Thanks. I'm not entirely sure it helps though - I only posted the
simplest case I could think of that demonstrates the problem, rather
than what I'm actually doing.

I can come up with other workarounds, but what I really want is a way
for a formula to enter a real blank into the cell, rather than an
empty string.

Matthew

A formula cannot enter a real blank in the cell.

First of all, the cell contains a formula so is not blank.

Secondly, "" is not null.

A truly blank cell is one which contains nothing.


Gord Dibben MS Excel MVP

On 18 May 2007 11:14:01 -0700, wrote:

On 18 May, 18:21, "Peo Sjoblom" wrote:
Use

=SUM(1,B1)

or

=N(B1)+1

Thanks. I'm not entirely sure it helps though - I only posted the
simplest case I could think of that demonstrates the problem, rather
than what I'm actually doing.

I can come up with other workarounds, but what I really want is a way
for a formula to enter a real blank into the cell, rather than an
empty string.

Matthew

Exactly WHY do you NEED (as opposed to WANT) a truly blank cell?

There are usually a number of ways to work around various scenarios where
zero length strings ( "" ) are returned.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
On 18 May, 18:21, "Peo Sjoblom" wrote:
Use

=SUM(1,B1)

or

=N(B1)+1

Thanks. I'm not entirely sure it helps though - I only posted the
simplest case I could think of that demonstrates the problem, rather
than what I'm actually doing.

I can come up with other workarounds, but what I really want is a way
for a formula to enter a real blank into the cell, rather than an
empty string.

Matthew