Displaying zeros as blanks

[Pradhan Balter]

I know this is an old question. Is there a way to display
zero fields as blanks? I know I can do this with and If
(x=0,""...etc, but then if this cell is used in a
subsequent formula, such as a sum, I get a VALUE error.

Thanks,
Pradhan

 

[Jacques Brun]

Pradhan,

Select:
Tools \ Options \ View Tab
Unselect <Zero Values>

Regards
Jacques

 

[CLR]

You can also wrap a SUM formula (or most any other formula) in an "IF", to
prevent zeros from being displayed.........

=IF(SUM(A1:A10)=0,"",SUM(A1:a10)

Vaya con Dios,
Chuck, CABGx3

 

[Ragdyer]

<<"but then if this cell is used in a subsequent formula, such as a sum, I
get a VALUE error">>

The SUM() function will still work with ( "" ) returned in a cell, included
in the sum range, from a formula.
You might have something else wrong to produce the #VALUE! error.

 

[macropod]

Apart from using Tools|Options|View and unchecking 'zero values', which
affects all cells on the worksheet, you can limit the effect to specific
cells with a custom number format. To do this, you could start with any of
the inbuilt number formats that have both +ve and -ve formats and simply add
a semi-colon (ie ';') to the end. Or you could roll your own, defining your
own +ve and -ve formats and adding a semi-colon (ie ';') to the end.

Cheers

 

[SidBord]

Sorry, but I don't understand your terminology . . . can
you elaborate? What's +ve and -ve?

 

[macropod]

+ve = positive
-ve = negative

Sorry, but I don't understand your terminology . . . can
you elaborate? What's +ve and -ve?