Excel Math

NikolinoDE  wrote:  ``Excel therefore stores 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.``

That describes only how Excel interprets numbers entered as text (manually type; copy-and-paste from text sources; import from a text file) and how Excel displays numbers.

But as I explain in a response in the cited thread, that is not true of calculated values.  And that is not specificied in the IEEE 754 (or any) standard.

Excel does not store only 15 decimal significant digits, despite the many online articles that say otherwise, including MSFT articles.  They are wrong!

In fact, Excel does not store decimal values, at all.  Instead, it stores binary values in memory.

It has been 5 years since I wrote that explanation, and I think I do a better job of it now.  LMK if you want more details.

But to demonstrate the point, consider the following example.

Enter 1234.567 into A1.  Enter =A1+0.000000000000455 or =A1+4.55E-13 into A2.  Format both as Number with 15 decimal places.  Note that the latter requires 19 decimal digits of precision.

Both display 1234.567000000000000.  But:

1. MATCH(A1,A2,0) returns #N/A, indicating that they are not the same binary value.

2. A1-A2=0 returns FALSE for the same reason. [1]

3. SUM(A1,-A2) returns about 4.55E-13, not zero. [2]

That demonstrates that Excel stores a binary value in A2 that must be represented by more than 15 significant digits.

In fact, the exact decimal representation of the binary value in A2 is 1234.56700000000046202330850064754486083984375 . [3]

-----

Possible TMI....

[1] But A1=A2 returns TRUE because of a trick that Excel plays to try to hide such infinitesimal differences.

[2] But =A1-A2 (only in formula form) returns exactly zero for the same reason.

[3] The exact decimal representation of A2 is 1234.567000000000462... instead of 1234.567000000000455... because A1 is not 1234.567000000000000..., in the first place.  Instead, the exact decimal representation of A1 is 1234.5670000000000072759576141834259033203125 .