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

Jonathan

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"NC" <[email protected]> wrote in message
news:[email protected]...
> 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

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