When you are creating a worksheet, and you format your cells to display information just the way you want, you may notice that you end up with “negative zero” amounts. Everyone learned in math classes that zero is not a negative number. So why does Excel show some zero amounts as negative?
The reason is because your formatting may call for displaying less information than Excel uses internally for its calculations. For instance, Excel keeps track of numbers out to fifteen decimal places. If your display only shows two decimal places, it is possible that a calculated value could be very small, and when rounded show as zero. If the calculated value is something like -0.000001325, then the value would show with only two digits to the right of the decimal point as -0.00. The negative sign shows, of course, because the internal value maintained by Excel is below zero.
There are a couple of ways you can solve this problem. The first is to simply round the calculated value to the desired number of decimal places. For instance, assume that this is your normal formula-the one that results in the “negative zero” values:
You can round the value in the cell by simply using the following formula instead:
This usage results in the value being rounded to two decimal places. In this way you should never end up with another “negative zero” value.
Another solution preferred by some people is to force Excel to use the same internal precision as what you have displayed in your worksheet. Just follow these steps:
- Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and click Options.)
- At the left side of the dialog box click Advanced.
- Scroll through the list of options until you can see the When Calculating this Workbook group. (See Figure 1.)
- Make sure the Set Precision As Displayed check box is selected.
- Click on OK.
Figure 1. The advanced options of the Excel Options dialog box.