Why is value in Excel file (2007+ types .xlsx etc.) stored as a different number in internal .xml file

Question

I am running Microsoft Excel 365 MSO (16.0.13530.20418 64-bit) running on Windows 10 64-bit.

As stated in the question my investigation is concerned only with .xlsx type Excel files where the underlying structure of the file consists of a set of .xml files.

I have been analysing this type of Excel file for some time now when I came across this for the first time!

THE PROBLEM

For any given cell in an Excel file there are usually two values, the unformatted value (the one the user enters in an Excel cell) and the formatted value (the one displayed by Excel in the cell when a custom format has been applied). As an example a user might enter a value of 6.452 into a cell. If the format of the cell is set to "General" then this is the value displayed by Excel. However if the user chooses to format the cell as a "number" to 1 decimal place Excel displays 6.5. If we now look inside the internal xml file for the cell we see that the value is stored as 6.452 and there will be a style assocaited with the cell to allow Excel to display the value as 6.5. This all makes sense.

However, this seems to change with the following example: Now the user enters a value of 3.4999 into a cell. If the format of the cell is set to "General" then this is the value, as before, displayed by Excel. Also, as before, if the user chooses to format the cell as a "number" to 1 decimal place Excel displays 3.5.

However, if we now look inside the internal xml file for the cell we see that the value is stored as 3.4998999999999998. As before, there will be a style assocaited with the cell to allow Excel to display the value as 3.5.

So, why has Excel stored the value 3.4999 that we entered into the cell as 3.4998999999999998 in the internal xml file?

I often read the contents of Excel files by reading their internal xml files. If I did this with the above example I would obtain different results to those displayed in the Excel file. Although the difference is extremely small, it is, none the less, non zero! I find this very unsatisfactory!

Also, surely it would have been easier, and more logical, to store it as the entered value 3.4999 instead of 3.4998999999999998?

question from:https://stackoverflow.com/questions/65952399/why-is-value-in-excel-file-2007-types-xlsx-etc-stored-as-a-different-number

Answer 1

Waitting for answers