Replacing zero values with dashes
 

I have a formula in a cell which is calculating from several cells.
When the value comes out at zero, I want a dash to display, not zeros.
I have read the tip on this subject, and tried it, but it does not work!
:( I have used the following syntax:

#,##0.00;[Red](#,##0.00);- ;

This should do the following:

1. When the result is positive, the display should have two decimal
places with a thousands separator.
2. When the results is negative, the display should have all the
above, but be red and have brackets around it.
3. When the result is zero, only a dash should be visible.

When my formula has a zero result, it shows "0.00" or "(0.00)"! Can
anybody tell me why?? :confused:

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=465821

Jonibenj Wrote:
I have a formula in a cell which is calculating from several cells.
When the value comes out at zero, I want a dash to display, not zeros.
I have read the tip on this subject, and tried it, but it does not work!
:( I have used the following syntax:

#,##0.00;[Red](#,##0.00);- ;

This should do the following:

1. When the result is positive, the display should have two decimal
places with a thousands separator.
2. When the results is negative, the display should have all the
above, but be red and have brackets around it.
3. When the result is zero, only a dash should be visible.

When my formula has a zero result, it shows "0.00" or "(0.00)"! Can
anybody tell me why?? :confused:

Jonathan

Hi Jonathan

Try this

_-*#,##0.00_-;[RED]-*#,##0.00_-;_-*"-"??_-;_-@_-


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=465821

Hi

Rather than changing the display format you can change the o/p
itself...

this can be done by using following formula

=if( yourformula=0,"-",yourformula)

this means when the answer is 0 it will return dash otherwise the
result.

added to this you can also keep the formatting
#,##0.00;[Red](#,##0.00) so that negative values would be shown as red

Regards
NC

Picky point:- XL Help suggests

#,##0.00_);[Red](#,##0.00) so that the positive numbers line up with the
negative numbers.

Incidentally a custom format of

#,###.00;[Red] (#,###.00);-;@

works for me in XL97
--
Regards

Sandy

Replace@mailinator with @tiscali.co.uk


"NC" wrote in message
oups.com...
Hi

Rather than changing the display format you can change the o/p
itself...

this can be done by using following formula

=if( yourformula=0,"-",yourformula)

this means when the answer is 0 it will return dash otherwise the
result.

added to this you can also keep the formatting
#,##0.00;[Red](#,##0.00) so that negative values would be shown as red

Regards
NC

Hi Paul,

Sorry, but Excel will not accept this format - it tells me to use one
of the custom formats!

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=465821

Dear NC,

This looked like a good option, however, it didn't work! I still got a
result of 0.00!
Unbelievable, eh?!! Any more ideas?

Jonathan


--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=465821

Jonibenj Wrote:
Hi Paul,

Sorry, but Excel will not accept this format - it tells me to use one
of the custom formats!

Jonathan

Hi Jonathan

I'm using excel 2000 and that is one of the custom functions, scroll
down the list it's either third from bottom or bottom of the list,
can't remember which


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=465821