Numbers stored in .XML file

SAPconsultant2095 

Nothing is wrong, so there is nothing we can do to "correct" the "problem" -- unless you want to store the data as text.

For example, with the value in A1, enter =TEXT(A1,"0,000") into A2.  If you wish, copy A2 and paste-special-value into A1 to replace numeric 99999,999 (in your language) with the string "99999,999" (in any language).

As I imply, the problem with that work-around is that the appearance of the number is not language-dependent.  You might consider that good or bad.  For example, the numeric value 99999,999, as you see it, correctly appears as 99999.999 to me, since I use period for the decimal point, not comma as you do.  But the string "99999,999" will always appear with a comma separator.  Consequently, formulas that treat the string as a number might fail when I open the Excel file.

------

Explanation....

Contrary to most online documentation (including MSFT's), numbers are not "stored with 15 significant decimal digits of precision".

Instead, numbers are represented in a binary form in memory, called 64-bit binary floating-point.  And since most decimal fractions (e.g. 0.999) cannot be represented exactly in that binary form, they are approximated by the sum of 53 consecutive powers of 2.

The reason that we see only up to 15 significant digits in Excel is because Excel arbitrary limits the display and formatting of numbers to that many digits.

But in fact, the 64BFP standard states that it requires 17 significant digits, rounded, in order to convert between all decimal and binary respresentations with no loss of precision.

So, Excel usually writes up to 17 significant digits into XML files.

The exact decimal representation of the binary approximation of 99999.999 is 99999.998999999996158294379711151123046875 .  The first 15 significant digits are in red; the next 2 significant digits (for a total of 17) are in green.

Thus, 99999.998999999996 is the correct 17-digit approximation of the exact binary representation of 99999.999.

And since that is correct, there is nothing we can do about it.

-----

That said, Excel is peculiar about how many fractional digits it does write into XML files.

For example, if the numeric value is 99999.99 instead of 99999.999, we see 99999.99 in the XML file instead of 99999.990000000005, even though the exact decimal representation is 99999.990000000005238689482212066650390625 .

I have not (yet) intuited a rule that explains when Excel chooses to write the 15-digit vs 17-digit approximation of a binary value when the value can be approximated accurately with just 15 significant digits.  I don't know if this is specified in any of the controlling specifications and standards.