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!

How about because it is a "User selectable option".

wrote:

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!

On Aug 5, 4:56*pm, Bob I wrote:
How about because it is a "User selectable option".

How so? Where whould I choose this option in Excel 2007?

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

wrote:

On Aug 5, 4:56 pm, Bob I wrote:

How about because it is a "User selectable option".


How so? Where whould I choose this option in Excel 2007?

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.

--
John C


"Bob I" wrote:

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

wrote:

On Aug 5, 4:56 pm, Bob I wrote:

How about because it is a "User selectable option".


How so? Where whould I choose this option in Excel 2007?

Bob

That just hides or displays zeros.

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


Gord Dibben MS Excel MVP

On Tue, 05 Aug 2008 11:19:03 -0500, Bob I wrote:

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

wrote:

On Aug 5, 4:56 pm, Bob I wrote:

How about because it is a "User selectable option".


How so? Where whould I choose this option in Excel 2007?

On Aug 5, 5:19*pm, Bob I wrote:
Office button, Excel Options, Advanced, Display options for this
Worksheet, Show a zero in cells that have zero value.

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 ****ed off at Excel, not at
you: I do appreciate your help :)

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.

wrote:
On Aug 5, 5:19 pm, Bob I wrote:

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


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 ****ed off at Excel, not at
you: I do appreciate your help :)

I'm ****ed off at Excel

Welcome to the club! <VBG

--
Biff
Microsoft Excel MVP


wrote in message
...
On Aug 5, 5:19 pm, Bob I wrote:
Office button, Excel Options, Advanced, Display options for this
Worksheet, Show a zero in cells that have zero value.

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 ****ed off at Excel, not at
you: I do appreciate your help :)

I suppose you may say that a Null has Zero value. It is not equal to a Zero.

John C wrote:

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.

I suspect "Null" is the operative term as opposed to "blank"

Gord Dibben wrote:

Bob

That just hides or displays zeros.

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


Gord Dibben MS Excel MVP

On Tue, 05 Aug 2008 11:19:03 -0500, Bob I wrote:


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

wrote:


On Aug 5, 4:56 pm, Bob I wrote:


How about because it is a "User selectable option".


How so? Where whould I choose this option in Excel 2007?

Exactly my point: NULL<0
--
John C


"Bob I" wrote:

I suppose you may say that a Null has Zero value. It is not equal to a Zero.

John C wrote:

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.

All instances of a excel worksheet have a style of 'Normal'. That includes a
general format for each cell....well the general format despite what it says
includes a number formatter..see
http://office.microsoft.com/en-ca/ex...488311033.aspx

You would have to create a custom format to show it as blank as the zero
value of the general value format is to show zero.

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

"desertres" wrote in message
...
All instances of a excel worksheet have a style of 'Normal'. That includes
a
general format for each cell....well the general format despite what it
says
includes a number formatter..see
http://office.microsoft.com/en-ca/ex...488311033.aspx

You would have to create a custom format to show it as blank as the zero
value of the general value format is to show zero.

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 :)

On Aug 5, 9:21*pm, "Peo Sjoblom" wrote:
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.

Amen to the database issue!

wrote:

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 :)

On Aug 5, 9:21 pm, "Peo Sjoblom" wrote:

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.

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

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.

Charles, Leeuwenburg wrote:

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

--

Dave Peterson