Why does linked data from blank cells become a zero?

M

MJ

When linking data within the same workbook (to different tabs) any cell that
is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I
want those cells to remain blank.
 
M

Max

That's just the way it is ..

Instead of : =Sheet2!A1
you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1)

Alternatively, we could suppress the display of extraneous zeros
in the entire sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK
 
E

eider

If you enter formula "=A2" and A2 is blank, Excel evaluates it as zero.

One way to get around this is to use =if(A2=0,"",A2). This will make the
cell appear to be blank, but will make some functions return an error.

If text creates an error in functions you are using, but a zero would not
distort the calculation (using addition and subtraction, but not averages,
etc.) you can leave formulas as you have them and go to tools-options, select
the "View" tab and uncheck "zero values" under window options near the bottom.
 
D

Dave Peterson

But this formula:

=if(A2=0,"",A2)
will also return "" if A2 actually contains 0 (not empty).

I'd use Max's suggestion and check for ""
=if(a2="","",a2)
 
G

Gord Dibben

MJ

You are doing nothing wrong. Excel does that for you.

Either turn off zeros through Tools>Options>View or.........

Enter a formula in your linked cells that change the values to blank.

=IF(Sheet1!A1="","",Sheet1!A1)


Gord Dibben Excel MVP
 
J

joeu2004

Max said:
you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1)
Click to expand...

But beware: the cell with this IF() function will no longer
be treated as zero if it is blank.

For example, if A:A (the entire column) has some blank cells
mixed with values, and B:B has =A1 etc, and C:C has =5*B1 etc,
then if you change B:B to =IF(A1="","",A1) etc as suggested
above, you must also change C:C to =IF(B1="","",5*B1) etc.

In other words, once you start propagating blank cells in the
manner suggested, you lose the ability for apparently "blank"
cells to be treated as zero. Apparently Excel treats only
truly empty cells as blank, not cells with the null string "".

Even though the test A1="" matches both the null string and
truly empty cells, apparently the null string "" is not the
same as an empty cell :-(.

At least, that has been my experience with Office Excel 2003.
If there is an option to treat the null string as an empty
cell -- or to treat any string as zero in an arithmetic
expression -- I would like to hear about it.

PS: I coulda sworn that some previous verions of Excel did
indeed treat a cell with only strings as zero when they are
referenced in an arithmetic expression. But perhaps my
memory is wrong.
 
M

Max

But beware: the cell with this IF() function will no longer
be treated as zero if it is blank.
Click to expand...

Yes, of course. And this may affect downstream calculations reading these
cells as you rightly pointed out. OTOH, we could also do it as:
=IF(Sheet2!A1="",0,Sheet2!A1), and then switch off the zeros display in the
sheet <g>.

To achieve a cleaner view, I'd usually just go for the option of switching
off the zero values display in the sheet, and leave the link formula as it
stands, w/o the error trap.
If there is an option to treat the null string as an empty
cell -- or to treat any string as zero in an arithmetic
expression -- I would like to hear about it.
Click to expand...

An example could be to use SUM() to avert downstream arithmetic errors,
since SUM seems to treat null strings/text as zeros:

Using : =SUM(A1:B1) instead of: =A1+B1
Using : =SUM(B1)-SUM(A1) instead of: =B1-A1

Believe the 1st expression is quite commonly used,
while the 2nd is probably not (it's just an example)
 
D

Dave Peterson

But if sheet1!a1 were numeric, =clean() just changed it to text.

(Even worse looking if sheet1!a1 held a date.)
 
Top