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

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:

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

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

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

Excel allows you to adjust spacing between cell walls and the contents of those cells. It does not, however, allow you to ...

Discover MoreHave you ever entered information in a cell only for it to appear as hash marks? This tip explains why this happens, how ...

Discover MoreExcel is continually trying to figure out what type of data is being stored in a cell. If it can interpret a value as a ...

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

2022-01-22 14:31:13

J. Woolley

="Precision is "&IF(PrecisionAsDisplayed(),"'AsDisplayed'","'Standard'")

My Excel Toolbox also includes the TogglePrecision macro to switch between the two possibilities.

Finally, the PrecisionToggleLink([Friendly_Name],[Screen_Tip]) function uses SuperLink to create a hyperlink that runs TogglePrecision when triggered. Optional Friendly_Name will be displayed; default is "Toggle Calculation Precision". The PrecisionAsDisplayed() function can be part of Friendly_Name and/or Screen_Tip. For example:

=PrecisionToggleLink(,"Now "&IF(PrecisionAsDisplayed(),"'AsDisplayed'","'Standard'"))

See https://sites.google.com/view/MyExcelToolbox/

2022-01-19 15:25:32

Gary

7.46

16.06

17.79

17.23

17.81

17.25

17.83

17.84

17.28

17.87

17.30

17.89

10.39

Then, sum all 13 numbers (210.00), subtract 199.61, then subtract 10.39. That should result in 0, but instead, Excel returns -1.42109E-14 or -.00000000000001421085. Why is Excel storing and using precision that was never entered? I'd like to trust that 2+2 = 4, but with Excel, one can never be sure that is the case. This is a bug. Interestingly, if you just subtract 210 (as opposed to 199.61 and 10.39), you get 0. Both calculations should return zero!

2021-03-26 10:01:27

David James Bonin

On some of the scorecards I produced, I show goal and result values.

- If the result meets the goal, I shade the result cell green.

- If the result is within, say, 10% of goal, I shade the result cell yellow.

- If the result is more than 10% short of goal, I shade the result cell red.

Depending on my spreadsheet, I may do the shading using conditional formatting or VBA.

For my readers' ease of use and to forestall questions about errors, I round goals and results to only the number of digits displayed before I compare them. That means the shading is applies based on the number displayed, and not the number in full precision.

Why do I do this? Because if the goal is exactly 2 (displayed as 2.00) and the result is 1.999820 (also displayed as 2.00), the result essentially meets the goal and I want it to be shaded green. Otherwise I get questions from readers about why a result that looks like 2.00 is shaded as if it isn't 2.00.

2020-05-27 16:07:12

Donald

Thanks ! This was very helpful.

2019-08-23 12:05:11

BrianM

Evidently there were a few trailing zeros somewhere, so your 'rounding' solution seems to be ideal.

Thanks again.

2019-01-14 08:26:33

Lew

Thank you, Allen, for helping to solve what was an annoying problem. Your article is excellent.

2015-10-04 10:29:59

Joe

2015-09-29 21:13:27

Neil

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

2015-09-29 10:34:28

E-Ted-E

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

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

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

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

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

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments