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

5. However, when we enter a number, Excel interprets only the first 15 significant digits, replacing any digits to the right with zero.  So if we type 9007199254740992, Excel does indeed store only 9007199254740990.

I once entered a bunch of sixteen digit credit card numbers and didn't notice that the last digit was being replaced by 0. There was no alert.

Shane.

Answer 4

Oh, I see the evil "TOU/COC violation" automated filters are back (or still broken, but not as much), corrupting legitimate strings of digits.

I'll edit my previous posting to replace the corrupted part with an image (sigh).

Answer 5

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.

Most articles on the subject, including the article cited by Jeganarayanan and the more-dispositive article that I cite, contain the same misinformation that contributes to users' misunderstanding, IMHO, namely statements like:  Excel stores 15 digits of precision; and calculation errros are caused by the IEEE specification of storing only 15 significant digits of precision.  In Jeganarayanan's reference, Jessica Liu compounds the misinformation by writing:  1234567890123456 cannot be exactly.

If Excel "stores 15 digits of precision", you would expect that 13502.15, 13472.75, and 13502.15 - 13472.75 = 29.4 can be represented exactly, since all of those numbers have much fewer than 15 digits.

But in fact, none of those statements is correct.  The following statements are.

1. The IEEE specification for 64-bit binary floating-point does not require storing 15 significant digits of precision.

In fact, the standard never mentions 15 digits; and it does not mandate "storing" [sic] any number of decimal digit.

Instead, the standard says:  it requires at least 17 significant decimal digits to represent all possible binary values and to convert between decimal and binary values without any loss of precision.

MSFT knows that.  That is why Excel uses up to 17, not 15, significant decimal digits to represent numeric values in XML files, which is how Excel stores "xlsx" and "xlsm" files.  (The "xlsx" or "xlsm" file is a compressed zip file that includes the XML files.)

Note:  Just because we can represent all possible binary values with up to 17 significant decimal digits, that does not mean that those 17 significant digits represent the binary value exactly.  I will demonstrate that below.

2. Most decimal fractions (and many very large integers) cannot be represented exactly in 64-bit binary floating-point.

Possible TMI....  Put another way:  even if a value is a rational number in decimal, most decimal fractions are not rational numbers in binary.

3. The estimation of a particular decimal fraction varies, depending on the magnitude of the numeric value.

Possible TMI....  That is because numeric values are represented as the sum of 53 consecutive powers of 2 ("bits").  The largest power of 2 depends, in part, on the magnitude of the value.  The larger the magnitude of the value, the fewer "bits" remain to represent the decimal fraction.  So, we might lose precision.

4. Using 64-bit binary floating-point, Excel can represent all integers between -2^53 and +2^53 exactly, which is the 16-digit value 9,007,199,254,740,992.

Note that that is significantly larger than Jessica Lui's example of 1,234,567,890,123,456.

5. However, when we enter a number, Excel interprets only the first 15 significant digits, replacing any digits to the right with zero.  So if we type 9007199254740992, Excel does indeed store only 9007199254740990.

FYI, VBA does not have that limitation for type Double.   So we can enter type Doublenumbers with greater precision in VBA.

6. Moreover, Excel formats only the first 15 significant digits (rounded), replacing any digits to the right with zero.

(FYI, VBA has the same limitation for type Double.)

For example, if we enter the formula =9007199254740990+2 into A1, Excel will store the result exactly (9007199254740992).  But it will display the result as 9007199254740990.  We can confirm that by noting that SUM(A1,-9007199254740990) results in 2, not zero.

I like to use the following example to demonstrate #2 and #3:  IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

The reason becomes apparent when we look at the exact decimal representation of the values on the right:

10.01       10.0099999999999,997868371792719699442386627197265625

10.01 - 10   0.00999999999999978,68371792719699442386627197265625

0.01         0.0100000000000000,0020816681711721685132943093776702880859375

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

Note the different approximation 0.01 in 10.01 and 0.01.  The first is less precise.  When we subtract the integer part, we are left with the less-precise approximation

So you see:  the result of 10.01 - 10 might be unexpected, but the calculation is not wrong.

Likewise, the exact decimal representation of your values on the right is:

The result of A1-A2 is correct, given the less-precise approximation of 0.15 in A1.

---

Possible TMI....  Just for grins, compare with the following result:

0.15-0.75+(13502-13472)           29.3999999999999,9857891452847979962825775146484375

0.15-0.75 can be calculated at full precision, and we lose precision only when we add the integer result of 13502-13472.  It is the same loss of precision in the constant 29.4.

Of course, I am not suggesting that as a workaround.  The point is:  the order of operations can make a difference.