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!

Zero values displaying as "-"
 

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

--

Regards,

Peo Sjoblom


"Uncle Ben" wrote in message
...
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!

Zero values displaying as "-"
 

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" wrote in message
...
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!

Zero values displaying as "-"
 

"Peo Sjoblom" wrote in message
...
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 windowscustom?

--

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

Zero values displaying 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.

Zero values displaying as "-"
 

Round it to 0 decimal places.

--

HTH

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


"Uncle Ben" wrote in message
...
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.

Zero values displaying as "-"
 

"Bob Phillips" wrote in message
...
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?

Zero values displaying as "-"
 

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
toolsoptionscalculation and check precision as displayed

--

Regards,

Peo Sjoblom


"Uncle Ben" wrote in message
...
"Bob Phillips" wrote in message
...
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?

Zero values displaying as "-"
 

"Bob Phillips" wrote in message
...
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