Displaying zeros as blanks

  • Thread starter Thread starter Pradhan Balter
  • Start date Start date
P

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
 
Pradhan,

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

Regards
Jacques
 
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
 
<<"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.
 
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
 
Sorry, but I don't understand your terminology . . . can
you elaborate? What's +ve and -ve?
 
Back
Top