# Cell shows 0.00 in different colors

#### Dan Wilson

##### Active Member
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...

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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.

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...

Replies
3
Views
520
Replies
2
Views
479
Replies
4
Views
160
Replies
8
Views
344
Replies
2
Views
64

1,203,632
Messages
6,056,439
Members
444,864
Latest member
Thundama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back