Getting Rid of Negative Zero Amounts

My Excel Toolbox includes the PrecisionAsDisplayed() function to return the status of a workbook's calculation precision (TRUE if 'AsDisplayed' or FALSE if 'Standard'). For example:
="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/

This makes sense -IF- I was entering numbers with more than 2 digits of precision or if I was doing a calculation that might result in more digits, but I am not. For example, enter the following numbers:

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!

I've done something similar for a similar reasons, but in an entirely different application.
 
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.

Thanks ! This was very helpful.

Thanks for resolving this 'problem' of negative zeros! I've tried all sorts of approaches, none of which were simple, and I wouldn't want to use a convoluted number format. Strangely (to me!) the amounts I was using were all simple currency amounts, personally input with no trailing zeros, yet a calculation of A1-B1 which produces a zero answer often display this as a negative, i.e. -0.00. (But sometime it didn't!)

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

Thanks again.

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

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.

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.

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?

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.

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

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.

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

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.

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