Replacing zero values with dashes

[Jonibenj]

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 decima
places with a thousands separator.
2. When the results is negative, the display should have all th
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)"! Ca
anybody tell me why??

Jonatha

 

[Paul Sheppard]

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??

Jonathan

Hi Jonathan

Try this >

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

 

[NC]

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

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Jonibenj]

Hi Paul,

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

Jonathan

 

[Jonibenj]

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

 

[Paul Sheppard]

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