Why does Excel think blank = zero ?

M

myemail.an

Hi all,

I have a number of columns which are linked to column A; specifically,
the values are linked to be the same (C2 = A2, etc). Why on earth does
Excel return a 0 when the source cell is, in fact, blank?
This is so wrong! Hasn't anyone explained to Bill Gates that zero and
null are two different things?
How do I get round Excel's error? So far the only solution I found is
using an If formula (=if(a2="","",a2) , but that's like killing a
mosquito with a nuclear bomb!
Any help would be greatly appreciated!
Thanks!
 
B

Bob I

Office button, Excel Options, Advanced, Display options for this
Worksheet, Show a zero in cells that have zero value.
 
J

John C

There is a point to be made. Sometimes I want a 0 to display, as it is a
value, and sometimes I don't if a cell is null, for example.
 
G

Gord Dibben

Bob

That just hides or displays zeros.

Does not explain why Excel treats a blank cell as zero.


Gord Dibben MS Excel MVP
 
M

myemail.an

Office button, Excel Options, Advanced, Display options for this
Worksheet, Show a zero in cells that have zero value.
Click to expand...

Thanks for the tip. However, it doesn't fully work. My situation is:
cell A2: blank ("")
cell B2: =A2
cell C2: =B2 & " some other text"

If I deselct the option you mention, B2 is shown as blank, but C2 is
still shown as "0 some other text". In other words, Excel still
considers B2 as containing a zero, regardless of whether it shows it
or not.
I still believe the whole concept is wrong: a blank is not a zero!
I found a solution using an if formula, but still believe it's all
absurd.
One more reason to use databases rather than Excel every time I can...

Thanks for your time, anyway!

PS just to avoid any misunderstanding, I'm pissed off at Excel, not at
you: I do appreciate your help :)
 
B

Bob I

In this case please put a single quote in A2. Excel is treating the
empty Cell as Null and showing that it has Zero value.
 
T

T. Valko

I'm pissed off at Excel

Welcome to the club! <VBG>

--
Biff
Microsoft Excel MVP


Office button, Excel Options, Advanced, Display options for this
Worksheet, Show a zero in cells that have zero value.
Click to expand...

Thanks for the tip. However, it doesn't fully work. My situation is:
cell A2: blank ("")
cell B2: =A2
cell C2: =B2 & " some other text"

If I deselct the option you mention, B2 is shown as blank, but C2 is
still shown as "0 some other text". In other words, Excel still
considers B2 as containing a zero, regardless of whether it shows it
or not.
I still believe the whole concept is wrong: a blank is not a zero!
I found a solution using an if formula, but still believe it's all
absurd.
One more reason to use databases rather than Excel every time I can...

Thanks for your time, anyway!

PS just to avoid any misunderstanding, I'm pissed off at Excel, not at
you: I do appreciate your help :)
 
P

Peo Sjoblom

The OP already explained that it has nothing to do with the display, it is
the contents which will not change
using a custom format.

--


Regards,


Peo Sjoblom
 
M

myemail.an

In another words, yet another confirmation that Excel sucks and should
be avoided whenever possible.
I do wonder how many mistakes would be avoided, and how many processes
would be sped up, if all Excel users learnt to use databases
properly :)
 
B

Bob I

Amen to the database issue!

In another words, yet another confirmation that Excel sucks and should
be avoided whenever possible.
I do wonder how many mistakes would be avoided, and how many processes
would be sped up, if all Excel users learnt to use databases
properly :)
Click to expand...
 
C

Charles Leeuwenburg

This quirk has driven me crazy through 5 versions of Excel. I often have a need for cells to return or repeat text strings based on the contents of cells contained in different worksheets or workbooks. It doesn't happen all the time (doesn't seem to be caused by various formatting of the cell - setting it as text or a number) but often when the cell recieving the data returns a "0" when the cell sending the data is empty. I've gotten around this by inserting a "space bar" key stroke in the cell sending the data. The esiest way to fix this problem (assuming that you're working with string text data and not numbers is to go into conditional formatting and set the cells to have a white font and not be seen or printed when the value in the cell is = 0. Hope this helps. An excellent link to explanation of conditional formatting:

http://www.contextures.com/xlCondFormat02.html

Hope this helps!
CL

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng
 
D

Dave Peterson

I think that using a space character is a mistake. That will ruin formulas that
look like:

=if(a1="","it's blank","it's not blank")

I use a formula like this:
=if(a1="","",a1)
if I don't want to see those 0's.

Using conditional formatting for this seems overkill to me.
 
Top