Number format doesn't recognize zero

Question

Greetings,

I have the following:

	A
1	13502.15
2	13472.75
3	=A1-A2
4	29.4
5	=A3-A4

Cell A5 has a custom number format:

0;[Red]0;[Blue]0;

The problem is that the result "0" in cell A5 is being formatted as a negative number (red) instead of zero (blue). How can I find out why?

If I change the numbers so that the total in A5 is still zero, the format correctly displays as zero. I don't know why these particular numbers result in Excel formatting zero as negative.

Thanks,

Shane.

Answer 1

Does anyone know a way to detect if a result has been miscalculated?

I do not agree that the calculation is wrong.  IMHO, only your perception of it is wrong because Excel only formats up to the first 15 significant digits (rounded), which is an arbitrary choice.

I can explain better (and more simply than any cited references).  But I don't have time at the moment.

To answer your immediate question....

1. Temporarily, format the cell value so that it displays 15 significant digits.

Shortcut: Highlight the calculation (it might be a subexpression) in the Formula Bar and press the f9 key.  Caveat: Be sure topress Esc afterwards, not Enter.  Otherwise, the formula will be changed irreversibly.

For example, if your values in A1 and A2 are constants, not the result of a calculation (even if you did copy-and-pasted value), when we format A3 with 13 decimal places, we see 29.3999999999996 instead of 29.4000000000000.

But sometimes, the infinitesimal difference is so small that we cannot see it because of Excel's arbitrary formatting limitation.

So, more generally....

2. =MATCH(--TEXT(A3,"0.00000000000000E+0"), A3, 0) returns #N/A when the binary representation of A3 is not the same as the binary representation A3 rounded to 15 significant digits.

We cannot do simply =A3=--TEXT(A3,"0.00000000000000E+0") because in Excel, the "=" operator rounds the left and right expressions to 15 significant digits for the purpose of the comparison.

3. =A3-TEXT(A3,"0.00000000000000E+0")-0,  formatted as Scientific, displays the infinitesimal difference between the two binary representations.

We cannot do simply =A3-TEXT(A3,"0.00000000000000E+0") because sometimes (and inconsistently), Excel replaces the actual arithmetic result with exactly zero (0.00E+00 when formatted as Scientific) in order to hide the infinitesimal difference.

That is incompletely described under the misleading title "Example when a value reaches zero" in https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel .

Of course, the redundant -0 should not make any difference.  But it does!  That demonstrates just how inconsistently Excel applies the rule.   (To be fair, it demonstrates that I simplified the conditions of the rule to some degreee.  There is more to it.)

Answer 2

Hi Shane

This is Floating point precision, the reason is quite ‘technical’: According to Microsoft, the reason for this wrong result is the so-called binary format which the numbers are converted to for calculation.

To understand more on this issue, please refer this link;

https://www.microsoft.com/en-us/microsoft-365/b...

Answer 3

To understand more on this issue, please refer this link;

https://www.microsoft.com/en-us/microsoft-365/b...

Thanks for the link. The article is hard to follow because the examples and tables are broken images.

I think I understand how precision is lost when irrational numbers are converted to binary. The article says "the calculation isn't wrong" when the calculation is wrong. Excel doesn't alert you when an operation has been miscalculated.

I discovered the error by accident. Now I can use the round function for this particular formula. Does anyone know a way to detect if a result has been miscalculated?

Thanks,

Shane.

Answer 4

To:  cShane

re:  custom number format

The problem may be Binary math.

The answer could be less than zero:  - 0.000000000003

'---

Excel programs (now free) at MediaFire...The Custom_Functions add-in has 20 new Excel functions including the X_vLookUp function. Download from...*http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents*

Answer 5

The value in A3 is not exactly equal to 29.4 due to tiny rounding errors that occur when Excel processes numbers. This is what you get when A5 is formatted as General and the column is wide enough:

To avoid this, round the result in A3 to 2 decimal places (since A1 and A2 have 2 decimal places), by changing the formula in A3 to

=ROUND(A1-A2,2)

The formula in A5 will now return exactly 0: