Zero values displaying as "-"

Hi everyone. I have created a new custom number format that replaces 0
values with a dash (-).

But there's a problem with this. In cases where the zero value is generated
from a formula that takes the value in one cell and subtracts the value in
another; and the result is 0, the 0 is in brackets (0).

I've tried everything under the sun, but no can do! Any clues anyone? TIA!

Are you sure it's zero, copy it to another call and paste special as values
and see what you get, byw how does your custom format look like copied from
the format windows>custom?

--

Regards,

Peo Sjoblom


"Uncle Ben" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone. I have created a new custom number format that replaces 0
> values with a dash (-).
>
> But there's a problem with this. In cases where the zero value is
generated
> from a formula that takes the value in one cell and subtracts the value in
> another; and the result is 0, the 0 is in brackets (0).
>
> I've tried everything under the sun, but no can do! Any clues anyone?
TIA!
>
>

Maybe your zero is not zero but some small number greater than 0. Increase
the decimal places on it and check.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Uncle Ben" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone. I have created a new custom number format that replaces 0
> values with a dash (-).
>
> But there's a problem with this. In cases where the zero value is
generated
> from a formula that takes the value in one cell and subtracts the value in
> another; and the result is 0, the 0 is in brackets (0).
>
> I've tried everything under the sun, but no can do! Any clues anyone?
TIA!
>
>

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> Are you sure it's zero, copy it to another call and paste special as
values
> and see what you get, byw how does your custom format look like copied
from
> the format windows>custom?
>
> --
>
> Regards,
>
> Peo Sjoblom
You're absolutely right, it's not zero, I have cents in there. The cells
are formatted to 0 decimals - so even though the sum is 0, Excel gives me
(0) because of the hidden decimals. How do I get cells where the decimal
value is >-0.50 to display as "-"?

Here is the formatting I'm currently using:

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

This allows me to display all zero amount cells as "-" instead of 0's.

The problem I have is for cells that contains small negative balances under
..50 - those cells do not format to "-" but to (0). So the report looks
silly, with -'s on all zero+'s and (0) on all zero-'s.

Is there a cure for this grand malaise? TIA.

Round it to 0 decimal places.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Uncle Ben" <[email protected]> wrote in message
news:[email protected]...
> Here is the formatting I'm currently using:
>
> _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
>
> This allows me to display all zero amount cells as "-" instead of 0's.
>
> The problem I have is for cells that contains small negative balances
under
> .50 - those cells do not format to "-" but to (0). So the report looks
> silly, with -'s on all zero+'s and (0) on all zero-'s.
>
> Is there a cure for this grand malaise? TIA.
>
>

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Round it to 0 decimal places.
>
> --
>
No, that's won't work, Bob, because the underlying numbers do carry two
decimal places. If I round to 0 decimal places, I'll end up with cents
elimination differences throughout the report. So because the input to the
spreadsheet is to two decimal places, I don't think I have much choice but
to round to 2 decimal places, but format to 0 decimal places (because for
reporting purposes, I don't want to see decimals.) Everything works 100%,
except for those -Zeros... I guess one quick way is just to go in and
override... but there has to be an easier way. I've been playing with the
IF function, that I could incorporate in the formula ... but I'm not quite
there yet. But thanks ... any other ideas?

There is an easier way but make sure you remember to turn this off when you
are not using this sheet, if you format for zero decimals you can go to
tools>options>calculation and check precision as displayed

--

Regards,

Peo Sjoblom


"Uncle Ben" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Round it to 0 decimal places.
> >
> > --
> >
> No, that's won't work, Bob, because the underlying numbers do carry two
> decimal places. If I round to 0 decimal places, I'll end up with cents
> elimination differences throughout the report. So because the input to
the
> spreadsheet is to two decimal places, I don't think I have much choice but
> to round to 2 decimal places, but format to 0 decimal places (because for
> reporting purposes, I don't want to see decimals.) Everything works 100%,
> except for those -Zeros... I guess one quick way is just to go in and
> override... but there has to be an easier way. I've been playing with the
> IF function, that I could incorporate in the formula ... but I'm not quite
> there yet. But thanks ... any other ideas?
>
>

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Round it to 0 decimal places.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)

Well Bob, I retract what I said earlier. Even though I was correct in my
response about rounding, your suggestion worked on that one column of the
spreadsheet where I was having this (0)'s showing. Even though the entire
spreadsheet is rounded to 2 decimal places, with 0 decimal on formatting,
that last column shows properly if I round it to 0 decimal places ... And
that should be fine, because all the numbers making up that total column
have been rounded to 2 decimal places. Thanks for your help and everyone
else who have come to the rescue. Much appreciated.

Regards,

Uncle Ben