Formulae returning blanks

[Matthew.Brown]

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

 

[Peo Sjoblom]

Use

=SUM(1,B1)

or

=N(B1)+1

 

[Matthew.Brown]

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

 

[Peo Sjoblom]

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

 

[Gord Dibben]

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

 

[Ragdyer]

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.