Why does the formatting of one cell show differently from the rest?

Benda D wrote:

Although none of our data has more than 2 digits after the decimal we tried your suggestion.  Unfortunately, this was not the answer.

I agree that Shane's formatting suggestion does not address the problem.

But Shane's explanation of the root cause of the problem is correct.  Cells formatted as Accounting will display $- only if the cell value is __exactly__ zero.  If the cell value is between zero and about +/- 0.0049, it will appear as $0.00 or $(0.00) when the cell is formatted to display 2 decimal places.

You can easily confirm that fact by entering 0.0049 and -0.0049 into such cells.  Or with your data, take Shane's suggestion:  temporarily for debugging purposes only, change the format of the cell to display, say, 17 decimal places.

You say that "none of our data has more than 2 digits after the decimal [point]".  That may or may not be correct, depending on facts that you have not provided.

Even if you have constants with only 2 digits, arithmetic with those constants might not result in exactly zero.  Consider this example:  if A1 is 10.10, A2 is 10.00, A3 is =A1-A2, and A4 is =A3-0.10, A3 will display $(0.00) instead of $-.

The reason is complicated to explain fully.  In a nutshell, it has to do with the way that Excel stores numbers and performs arithmetic (called binary floating point).  See KB 78113 athttp://support.microsoft.com/kb/78113 .

However, it is also important to note that if you have formulas that display 2 decimal places because of your formatting choice, the actual underlying value might have more decimal places (unless you have the Precision As Displayed calculation option set, which should not be recommended without a lot of caveats).

For example, if A1 is =1/3, it will display as $0.33 when the format is Account with 2 decimal places.  But =100*A1 will display as $33.33, demonstrating that what appears to be $0.33 is really at least 0.3333 (and really about 0.333333333333333).  WYSI(not)WYG. ;-)

To correct the situation, I suggest that you explicitly round any formula that you want to result in "exact" dollars and cents.  In my examples, A3 should be =ROUND(A1-A2,2) and A4 should be =ROUND(A3-0.10,2).

Alternatively, it is tempting set the Precision As Displayed calculation option.  That does avoid having to round explicitly in most (but not all) cases.  But if you choose to experiment with PAD, be sure to make a copy of your Excel file first.  PAD can cause undersirable changes in constants, which are irreversible.

It most likely is the rounding so just add a nested argument into your formula. Write, =rounding(then your current formula, and at the end place a comma and the number of decimals. Lets assume its 2 decimals. your formula would be:

=**Rounding(**sum(a1:e1),2).

Where the bolded sections are additions. This should do it. If not, and I have this issue all the time, then you might need to add another column/row and try that, possibly having to manually type the result into the cells that are not cooperating.

Probably the number is slightly negative and therefore showing as (0.00)  for example if you have =A1-A2 and the difference is -0.00001 it may be displaying as $(0.00).  If you increase the decimals you may eventually see the small negative amount. 

While on the cell Choose Format, Cells, pick Custom and note that there is a format for positive , negative and zero values.  For accounting notation it would look like this:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

for numbers

#,##0.00_);(#,##0.00)

for Currency

$#,##0.00_);($#,##0.00)

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Actually we need to reconsider the original post - the Format Cell dialog box is displaying:

"$-"

The spreadsheet is displaying

"$(0.00)". 

If $- is what you see in the Format Cells dialog box then seeing $(0.00) in the spreadsheet is not related to decimal place errors.  The format in the dialog box suggests that if Excel sees a negative number, it should display it with a minus sign.  Since it is displaying as $(0.00) not as $- that suggests that Excel sees the number as 0 or positive and formats it with parens.  That is certainly not a standard format, but it is possible.

The decimal place discussion I initiated and joel followed misread this.  If the number is negative, no matter how small, the format suggests it should dispay with a - not ()'s.  We have jumped to the standard response where the user believes the number should be 0 but it is displaying as -0 or (0).  This is not the case stated in your original post.

Please do the following:

1.  Select the offending cell and choose Format, Cells, Number tab, Custom

2.  Copy the contents of the Type line to here so we can see it.

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Although none of our data has more than 2 digits after the decimal we tried your suggestion.  Unfortunately, this was not the answer.