Cell shows 0.00 in different colors

[Dan Wilson]

Good day all. I am using Excel 2003 on Windows XP Pro. I have a worksheet that I use to reconcile my cash register. In the worksheet there are several places where the value of 0.00 is a valid response to a formula. In the cells in question, I am using a custom format

###0.00;[Red]-###0.00

This format will print results above 0.00 in black and results below 0.00 in red. Sometimes the 0.00 will show in black and other times in red with a minus sign in front of it. I have tried several combinations of the data in the cells being calculated to create the 0.00 result, but nothing seems to change the results.

Am I doing something wrong? Do I need to change the custom format?

Any help is appreciated.
Thanks, Danno...

 

[Richard Schollar]

Hi Dan

This problem can be avoided by rounding your formula result - it is the result of Excel (and indeed any computer) holding numeric floating point numbers in binary whilst only having a limited precision (in Excel's case to 15 digits).

An alternative would be to select the "Precision as Displayed" option from Tools>Options, however this should be approached with caution as it involves a permanent loss of precision.

When Excel is colouring your cell red (ie 0.00 is red) what it actually means is that the value in the cell is something like:

-0.000000000000001

or some such, and whilst the display is set to only show the first two decimal digits, the actual number is retained as this very small negative value.

Hope this helps make sense of the situation!

Richard

 

[fairwinds]

Hi,

I guess your numbers are not exactly zero. It could be that you have more decimals than it is formatted to show. Depending on your calculations this could happen due to the floating point arithmetic that excel uses when calculating. You could use a ROUND function to avoid this.

 

[Dan Wilson]

Reply

Good day Richard and Fairwinds. Thanks for the help, I can always count on the Board Members. As you both suggested, the problem is the data not being absolute zero. I expanded the cell width and formatted the cell to show 8 decimal points and there it was! A single "1" sitting about 6 decimal places down. After adding ROUND to my formulas, the problem went away.

I am interested in the "precision formatting" that you mentioned Richard. Most of the work that I do in Excel deals with money. Thus, I do not need more than two decimal points of precision. You mentioned that changing the precision affects everything. Does that mean that it affects all other excel workbooks, the entire computer, or just the current worksheet?

I do have some other worksheets that utilize the precision as far as four decimal places for recipes, cost calculation, etc. In those worksheets, I use the ROUND function quite a bit.

Thanks, Danno...