Zero values displaying as "-"

[Uncle Ben]

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]

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?

 

[Bob Phillips]

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]

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

 

[Uncle Ben]

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]

Round it to 0 decimal places.

--

HTH

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

 

[Uncle Ben]

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?

 

[Peo Sjoblom]

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

 

[Uncle Ben]

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