Why are there inconsistencies in the display of zeroes in account.

[Sousixer]

As a CPA, I often use the accounting format with no dollar sign. Sometimes a
cell which contains a formula resulting in zero will show the zero as -
(which I prefer). Other times the zero will be shown as 0.00 or even (0.00).
Why the differences and how do I make it always show a zero as - ?

Thanks in advance for anyone's help.

 

[Huber57]

Sousixer,

To change a '0' to '-'. Highlight all of the cells you wish to format and
then go to Format cells (by right-clicking or Ctrl+1 or Format | Cells).
Select the Accounting category in the left side of the window. On the right
side, in the Symbol dropdown menu, choose None.

HTH.

 

[Gord Dibben]

My first guess would be you don't really have a 0 in that cells that display
0.00

Increase the DP out a ways. You might have .00000123 or similar.

Accounting format will always display that as 0.00

You may have to do some ROUNDing in your formulas to get rid of the
..00000123


Gord Dibben MS Excel MVP

 

[Fred Smith]

It's because you don't have zero in the cell, but it's less than 0.005.
Excel is rounding this to 0.00 as you requested in your format. To get the -
displayed, round your calculations to 2 decimal places.

Regards,
Fred

 

[Joe User]

I often use the accounting format with no
dollar sign. Sometimes a cell which contains
a formula resulting in zero will show the zero
as - (which I prefer). Other times the zero will
be shown as 0.00 or even (0.00).

The "-" appears only when the value is exactly zero. The value of the cells
that appear as 0.00 or (0.00) is not exactly zero.

This arises one of two ways:

1. Your formula might result in numbers with more decimal places. Common
case: multiplying by a percentage (e.g. interest rate).

2. Infinitesimal "errors" (aberrations) are introduced in arithmetic
operations because most numbers with decimal fractions cannot be represented
exactly internally. For example, =10.1-10 is not exactly the same as the
constant 0.1. Try =IF(10.1-10=0.1,TRUE). It returns FALSE(!).

Fix this one of two ways:

1. Use the ROUND function in formulas, at least in any formula that should
result in a dollars-and-cents value; i.e., =ROUND(formula,2).

Do not rely on formatting with 2 decimal places. That rounds the
__appearance__ of the value. It does not round the actual value.

2. Use the "Precision as displayed" option (PAD) under Tools > Options >
Calculation (in Excel 2003).

Generally, I deprecate the use of PAD because it is applied
indiscriminately and because setting the option can change constants cell
values (e.g. interest rates) irreversibly. If you choose to try PAD, be sure
to make a backup copy of the Excel file before setting the option.


----- original message -----

 

[Joe User]

For example, =10.1-10 is not exactly the same as the
constant 0.1. Try =IF(10.1-10=0.1,TRUE). It returns FALSE(!).

A better example that is more on point:

=10.1-10-0.1

does not result in __exactly__ zero.


----- original message -----