Question for Mark W - applying custom format 0;-0

Posted by Esau on January 20, 2002 6:04 PM

Mark
I am trying to use a custom format you suggested (0;-0)at
15910.html to stop '0' appearing when the range of cells to which the formula refers is blank. When I apply this custom format to the cell containing the formula it still returns a zero instead of showing as 'blank'. Any ideas?
Thanks

Posted by Jacob on January 20, 2002 6:31 PM

On Tools|Options there is a zero value checkbox , uncheck it to hide zero values. Also you can conditional format the cells to white font if the value is zero, a third option would be to add a check in the formula to see if the value is zero the put "" if it is, i.e. If(MyFormual = 0,"",MyFormula)

HTH

Jacob

Posted by Esau on January 20, 2002 7:34 PM

Thanks Jacob - solves my problem, but...

Your first suggestion will apply to the whole spreadsheet, which in this case doesn't exactly suit my needs. nice tip though. Also, I can't use the "" solution because it won't work with an arithmetic function I am using.
I did like your lateral use of conditional formatting though - I can certainly use that one.
I'm still curious as to why the formatting won't work though.

Thanks again for your help

Posted by Aladin Akyurek on January 20, 2002 10:54 PM

Or...

Custom format the cell of the formula that is allowed to return 0 as

[0]"";General

Aladin

=========

Posted by Mark W. on January 21, 2002 7:19 AM

Use 0;-0;

I failed to type the trailing semicolon. BTW
the definitive Help topic on custom number formats
is "Custom number, date, and time format codes".
It explains that every number format is composed
of 4 parts separated by semicolons. This
help topic is well worth a read. I have it
bookmarked.

Posted by Esau on January 21, 2002 3:15 PM

Thank you Mark, Aladin and Jacob (nt)