A reference to a blank cells evaluates to 0

[nunoferreira]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

Here is a very very very simplified version of a problem that is annoying me:

Cell A1 is blank ("=isblank(A1)" typed in some other cell evaluates to TRUE)
Cell A2 is blank ("=isblank(A2)" typed in some other cell evaluates to TRUE)

Cell A3 has "IF(A2<>"",a*b,a*c)"
By which I mean "If data is available mutiply a by b otherwise multiply a by c"

Now, for some reason, I don't want to enter data directly in cell A2, I want to enter data in cell A1 and automatically propagate those data to cell A2 (in the actual problem, cell A1 is in one sheet and there are many cells A2 in several other sheets. For obvious reasons, I want to enter data only once, that is why I need all of the A2 cells to refer to cell A1)

So I type in cell A2 "=A1". The problem is that when cell A1 is still blank (no data) cell A2 gets a 0 (zero, which IS data). Cell A3 consequently does a*b (data - 0 - IS available) instead of a*c (data is NOT available)

To work around the problem I have to type in cell A2 "IF(A1="","",A1).

It seems I have to accept that the simplicity of typing in cell A2 "=A1" is not in the realm of possibilities. But is there anything simpler than "IF(A1="","",A1) ?

Thanks in advance for any help available.

P.S. Just by curiosity: does any one know, or remember, what was the rationale for XL to make a cell that is declared to be equal to a blank cell to "inherit" a 0?

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

Here is a very very very simplified version of a problem that is annoying me:

Cell A1 is blank ("=isblank(A1)" typed in some other cell evaluates to TRUE)
Cell A2 is blank ("=isblank(A2)" typed in some other cell evaluates to TRUE)

Cell A3 has "IF(A2<>"",a*b,a*c)"
By which I mean "If data is available mutiply a by b otherwise multiply a by
c"

Now, for some reason, I don't want to enter data directly in cell A2, I want
to enter data in cell A1 and automatically propagate those data to cell A2 (in
the actual problem, cell A1 is in one sheet and there are many cells A2 in
several other sheets. For obvious reasons, I want to enter data only once,
that is why I need all of the A2 cells to refer to cell A1)

So I type in cell A2 "=A1". The problem is that when cell A1 is still blank
(no data) cell A2 gets a 0 (zero, which IS data). Cell A3 consequently does
a*b (data - 0 - IS available) instead of a*c (data is NOT available)

To work around the problem I have to type in cell A2 "IF(A1="","",A1).

It seems I have to accept that the simplicity of typing in cell A2 "=A1" is
not in the realm of possibilities. But is there anything simpler than
"IF(A1="","",A1) ?

Thanks in advance for any help available.

P.S. Just by curiosity: does any one know, or remember, what was the rationale
for XL to make a cell that is declared to be equal to a blank cell to
"inherit" a 0?

Yes, this is a "feature" of Excel that goes back to the beginning of time.
It's just the way it works, and there is no changing that. I suppose the
rationale is that if you are evaluating a formula, the result is a number,
thus the zero in the null case.

There probably is no way to get around this. I use =if(len(a1)=0,"",A1), but
your formula is fine too.

 

[nunoferreira]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

Here is a very very very simplified version of a problem that is annoying me:

Cell A1 is blank ("=isblank(A1)" typed in some other cell evaluates to TRUE)
Cell A2 is blank ("=isblank(A2)" typed in some other cell evaluates to TRUE)

Cell A3 has "IF(A2"",a*b,a*c)"
By which I mean "If data is available mutiply a by b otherwise multiply a by
c"

Now, for some reason, I don't want to enter data directly in cell A2, I want
to enter data in cell A1 and automatically propagate those data to cell A2 (in
the actual problem, cell A1 is in one sheet and there are many cells A2 in
several other sheets. For obvious reasons, I want to enter data only once,
that is why I need all of the A2 cells to refer to cell A1)

So I type in cell A2 "=A1". The problem is that when cell A1 is still blank
(no data) cell A2 gets a 0 (zero, which IS data). Cell A3 consequently does
a*b (data - 0 - IS available) instead of a*c (data is NOT available)

To work around the problem I have to type in cell A2 "IF(A1="","",A1).

It seems I have to accept that the simplicity of typing in cell A2 "=A1" is
not in the realm of possibilities. But is there anything simpler than
"IF(A1="","",A1) ?

Thanks in advance for any help available.

P.S. Just by curiosity: does any one know, or remember, what was the rationale
for XL to make a cell that is declared to be equal to a blank cell to
"inherit" a 0?

Yes, this is a "feature" of Excel that goes back to the beginning of time.
It's just the way it works, and there is no changing that. I suppose the
rationale is that if you are evaluating a formula, the result is a number,
thus the zero in the null case.

There probably is no way to get around this. I use =if(len(a1)=0,"",A1), but
your formula is fine too.
[/QUOTE]
Thanks Bob for sympathizing.

Now the purists will say, the way to signify the absence of data in a cell is not to leave it blank is to enter #N/A (and the Help file for Excel, under the topic "#N/A" has obviously been written by such a purist )

But when you have a table with years 2000 - 2010 in columns and Jan to Dec in rows and that table is meant to be filled-in a cell every month (when the value for that month becomes available), in October 2008 (today) you're going to have a lot of cells (from Nov 2008 to Dec 2010) with #N/A. Would you really dare presenting that worksheet to a non-Excel-savvy audience?

Cheers

Nuno Ferreira

 

[Bob Greenblatt]

Thanks Bob for sympathizing.

Now the purists will say, the way to signify the absence of data in a cell is
not to leave it blank is to enter #N/A (and the Help file for Excel, under the
topic "#N/A" has obviously been written by such a purist )

But when you have a table with years 2000 - 2010 in columns and Jan to Dec in
rows and that table is meant to be filled-in a cell every month (when the
value for that month becomes available), in October 2008 (today) you're going
to have a lot of cells (from Nov 2008 to Dec 2010) with #N/A. Would you really
dare presenting that worksheet to a non-Excel-savvy audience?

Cheers

Nuno Ferreira

No, but what does this have to do with your original post or my response to
it?

 

[chezmah]

dont like all those 0.00 in every row and column where there is nothing to post. TOO much ink and too much useless info. Is there a way to select a whole sheet and replace all the 0.00s with just a blank cell? There are just too many of them to do them individually by hand. I'm a beginner and have inherited a document that was constructed by someone other than myself. I find it confusing to read and way too cluttered.
Any help would be appreciated !! thanks

 

[JE McGimpsey]

dont like all those 0.00 in every row and column where there is nothing to
post. TOO much ink and too much useless info. Is there a way to select a
whole sheet and replace all the 0.00s with just a blank cell? There are just
too many of them to do them individually by hand. I'm a beginner and have
inherited a document that was constructed by someone other than myself. I
find it confusing to read and way too cluttered.
Any help would be appreciated !! thanks

One way:

Preferences/View, uncheck the Show zero values checkbox.

Alternatively:

Instead of

=<your formula>

use

=IF(<your formula> = 0, "", <your formula>)

 

[gkhanson]

I have always used the 'If' statement to populate a spreadsheet when I want to fill in values later, to avoid 'error' statements when division by zero occurs. Excel for Mac 2008 seems to have issues that previous versions did not. In the following example, I want cell B5 to appear blank until I enter a value into cell C5. When I enter in to cell B5 the following:

=IF(LEN(C5)=0,"",C5/H5)

I get a #VALUE! message, even though cell C5 is blank.

What I'm trying to do is take stock trade data from other cells, and figure profit, loss and %. I'd like to set the sheet up so that it appears blank until I enter the data into the first cells, so that Excel does all the calculations without my having to cut and paste each time I enter a new row of data.

Can you help?

 

[JE McGimpsey]

I have always used the 'If' statement to populate a spreadsheet when I want
to fill in values later, to avoid 'error' statements when division by zero
occurs. Excel for Mac 2008 seems to have issues that previous versions did
not. In the following example, I want cell B5 to appear blank until I enter a
value into cell C5. When I enter in to cell B5 the following:

=IF(LEN(C5)=0,"",C5/H5)

I get a #VALUE! message, even though cell C5 is blank.

What I'm trying to do is take stock trade data from other cells, and figure
profit, loss and %. I'd like to set the sheet up so that it appears blank
until I enter the data into the first cells, so that Excel does all the
calculations without my having to cut and paste each time I enter a new row
of data.

You get the #VALUE! error if either argument in C5/H5 is text. So if you
"clear" C5 cell by using the spacebar, you'll get #VALUE!

One alternative:

=IF(ISNUMBER(C5),C5/H5,"")

or, if H5 may be zero or non-numeric:

=IF(AND(ISNUMBER(C5),ISNUMBER(H5),H5<>0),C5/H5,"")