Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Getting Rid of Negative Zero Amounts.

Getting Rid of Negative Zero Amounts

by Allen Wyatt
(last updated September 29, 2015)

9

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:

=SUM(A3:A23)

You can round the value in the cell by simply using the following formula instead:

=ROUND(SUM(A3:A23),2)

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:

  1. 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.)
  2. At the left side of the dialog box click Advanced.
  3. Scroll through the list of options until you can see the When Calculating this Workbook group. (See Figure 1.)
  4. Figure 1. The advanced options of the Excel Options dialog box.

  5. Make sure the Set Precision As Displayed check box is selected.
  6. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9901) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of Negative Zero Amounts.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Finding Other Instances of Excel in a Macro

When processing information using a macro, you may need to know if there are any other instances of Excel running on a ...

Discover More

Using Macros in Protected Workbooks

Having problems with using macros in a protected workbook? There could be any number of causes (and solutions) as described ...

Discover More

Using Bookmarks

Bookmarks are helpful in defining locations in your document that you can later link to. Here's the story on how to use them ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Deleting Unwanted Styles

Custom styles can be a great help in formatting a worksheet. You may, at some point, want to get rid of all the custom styles ...

Discover More

Specifying Superscript Text

Applying different formatting to the text within a cell can seem a bit confusing. This is certainly the case when it comes to ...

Discover More

Setting Vertical Alignment

Excel allows you to adjust not only the horizontal alignment of values in a cell, but also the vertical alignment. This tip ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 1?

2015-10-04 10:29:59

Joe

A quick word to the wise about using "Set Precision As Displayed". I was annoyed with some calculations not giving true zero on one of my cost tracking spreadsheets, so I turned on "Set precision as displayed", with formatting set to show 2 decimal places and all was good. However a couple of months ago I opened the spreadsheet and all the decimal parts of my costs (the pence) had reset to zero (even when looking in the formula bar). I still don't know if this was a bug or I did something silly (quite possible), but I'm sure it is connected with having turned on "Set Precision as Displayed" as it is the only way I know of for Excel to lose this level of precision. Fortunately I had backups which meant I could rebuild the sheet, but it took a couple of hours to move the later additions over to the backed up sheet. I now just round the results of calculations to 2 decimal places instead.


2015-09-29 21:13:27

Neil

Sir Andrew,

This comes about as the calculation is either exactly 0.0000000000 so you get the hyphen or if it is say 0.000000001 then will be displayed as 0.00 if you are using the standard comma style format.

The simplest fix is to use a round function, for example =round(value, 9). I usually use round to a large number of decimals as it can be useful to see 0 as it may indicate an error (say if the result is 0.1 and I am expecting 0.00000).

The issue mainly occurs when you are subtracting - is often caused by numbers being exact in decimal but in binary it may not be. For example 0.1 in decimal when written as binary is 0.00011001100110011001100110011001100110011001100110011001100110...

This can be an issue across many applications - not confined to Excel.


2015-09-29 11:59:10

Sir Andrew Thomas

This is slightly related, but still different. I have the experience that zero results of one or another calculation, including additions and subtractions, show sometimes as a hyphen and sometimes as a zero, even though the cells are all formatted the same. Does anyone know why or how to fix this? Is this the result of their being some amount way past the decimal as in the above article?


2015-09-29 10:34:28

E-Ted-E

Micky A: you didn't go wrong. That is what checking the "Set Precision as Displayed" function does. All values are "remembered" by Excel as exactly the value(s) shown. So the value that had been negative .00000123 is now set to be zero, which is not negative.

NOYB: you are way too "absolute" in your opinion. There are valid reasons some values are expressed in specific levels of "rounding". Briefly, the values must not imply a level of precision not supported by the data &/or by the equipment.
If you measure a board as 4.4 cm by 2.3 cm, the area of the board is 10.1 sq-cm. To say it is 10.12 sq-cm suggests you are sure it is not 10.14, nor 10.10 - which you do NOT know, as your tools are not that precise.


2014-08-08 08:05:13

Bryan

Ok I wrote up a whole big rant about how you were wrong, and then I realized you weren't talking about *significant digits*! (Precision has never been relevant for any of my work, since any calculators or programs I've worked with have dealt with it well enough to not cause problems; significant digits, however, are trickier to deal with in Excel, and are something I've been concerned with more).

I still think you are wrong though, just in a different way.

Excel already does the math the way you think it should. In fact, it does one better: it keeps 15 significant digits for any calculation. The only thing it doesn't do is DISPLAY the number of digits you think it should... except when it does. Try typing "=3.0403 + 0.45" into a cell formatted as General and see what displays. 4 decimals, right? Now type "=3.0403 * 0.45" into a General cell -- you'll get the proper 6.

Now there are some cases where it won't *show* all the decimals. It's hard to repeat but there's some sort of algorithm that starts to round when the number of decimals exceeds the width of the cell, but it won't mess up your calculations. Make the format "0.0" or "#,##0", or even "Elephants", and perform the same test as above. You will see a different number (3.5/1.4, 3/1, and Elephants/Elephants, respectively), but now reference that cell in another calculation in another cell: you get the correct result!

In conclusion, I have no idea what you are talking about when you say that Calculator works better than Excel. Even if I did, saying MS can't figure it out doesn't make sense, since they make both programs...


2014-08-07 16:08:48

NOYB


Regarding addition, subtraction, and multiplication there is one and only one correct result precision.

Addition and subtraction always results in exactly the same decimal point precision as that of the operand that has the greatest precision. Period. No if's and or buts.

Multiplication always results in exactly the decimal point precision of the sum of the precision of the operands. Period. No if's and or buts.

Any application that produces results with precision level other than this is just plan wrong and injecting errors into calculations. Especially when those results are also used as is in more downstream calculations. Continuing to compound the error.

It is pathetic that a company such as Microsoft cannot do this correctly in a business class product such as Excel after all these years.

Even The lowly Windows Calculator can do this correctly.

The very least Microsoft could do is provide an option setting and/or function to produce result precision for these operators accordingly.

Can't believe people in the business, and especially engineering disciplines are so accepting of this and willing to always have to be working around it. It's pathetic mathematical nonsense.


2014-08-06 07:11:07

Bryan

NYOB: That sort of functionality would be a complete change to how precision is done, and since every discipline does precision in a different way, if MS picked one, many people would complain that the wrong one was picked.

The fact of the matter is, if the value in the cell is -0.0000123 and Excel shows -0.0000, this isn't "wrong", just somewhat counterintuitive if you don't understand what's going on.

That being said, I agree with Micky; I can't reproduce the error when using "Number" format. If you use a custom formatting of "0.00000;-0.00000;0.00000", then I get the behavior Allen described... but that's because I basically told Excel to give me the "error".


2014-08-05 15:46:47

NOYB

Microsoft could alleviate a significant part of this issue by checking the operator and applying some simple rules accordingly.

If the operator is add or subtract then the result precession should the same as the operand that has the greatest precession. For instance. The precession of 3.0403 + 0.45 should be 4 decimal places.

If the operator is multiply then the result precession should be the sum of the precession of the operands. For instance. The precession of 3.0403 x 0.45 should be 6 decimal places.

I'm sure a math wizard could figure out appropriate rules for other operators as well.

Even the lowly Windows calculator knows better than to display zero as a negative value. Try it with some of your Excel calculations that result in a negative zero.


2014-08-03 11:38:37

Michael (Micky) Avidan

This is very strange ...
I just checked the above, on four versions of 'Excel' ["2003/2007/2010/2013"] Whilethe 'Set Precision As Displayed' check box is NOT selected.
Summing a range of cells ended up with: -0.00000123 and after reducing the amount of decimals to 5 - it displayed a value: 0.00000 without(!) Minus sign.
What or where did I went wrong ?
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.