Excel XML worksheet export has unprecise numbers

Question

I use the "Excel 2004 Worksheet" format to export data for further processing (using XSLT or PHP).

However, I noticed that Excel 2016 for Mac will export strange, unprecise numbers.

If I create a spreadsheet with (precise) numbers like so:

100,00
100,10
100,20
100,30
100,40
100,50
100,60
100,70
100,80
100,90
68,40

All numbers except the last one will be exported correctly:

  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="11" x:FullColumns="1"

   x:FullRows="1" ss:DefaultRowHeight="16">

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.1</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.2</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.3</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.4</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.5</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.6</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.7</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.8</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">100.9</Data></Cell>

   </Row>

   <Row>

    <Cell ss:StyleID="s62"><Data ss:Type="Number">68.400000000000006</Data></Cell>

   </Row>

  </Table>

Due to my tests there are certain numbers that are always exported incorrectly, while others will always be exported correctly.

68,40 is a good number to test with.

Unfortunately, I already removed Excel 2011 from my Mac, so I can't compare the behaviour.

Answer 1

Michael wrote:

All numbers except the last one will be exported correctly:

[....]

"><Data ss:Type="Number">68.400000000000006</Data>

---

Actually, the number is more, not less, precise.

And there is nothing wrong.  That is actually the decimal approximation of the binary value that is stored internally in Excel.

The extra decimal places are unexpected, albeit correct, for several reasons.

First, Excel format (displays) only up to the first 15 significant digits (rounded).  So the constant 68.4 appears to be 68.400000000000000 even when formatted to display 15 decimal places (17 significant digits).

But numerical values are stored internally using 64-bit binary floating-point, and it requires 17 significant digits to convert between decimal and binary representations with no loss in precision, according to the IEEE 754 standard.

So Excel formats up to the first 17 significant digits (rounded) in the XML file, in part because (compressed) XML is used to repesent saved "xlsx" and "xlsm" Excel files.

The 64-bit binary floating-point representation of numerical values is the sum of 53 powers of 2 ("bits") times an exponential factor. Consequently, most decimal fractions cannot be represented exactly.  Moveover (although not relevant for this question), the approximation of the same decimal fraction varies, depending on the magnitude of the integer part.

For example, the decimal approximation of the constant 68.4 is exactly 68.4000000000000,05684341886080801486968994140625, which is 68.400000000000006 when rounded to 17 significant digits.

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

Answer 2

Michael wrote:

Thanks a lot! I understand the technical background now.

You're welcome.  I meant to add the following, which shows the exact decimal approximation of the other values with decimal fractions.

Note, for example, that 100.2 rounds to 100.20000000000000, which is why it is simply 100.2 in the XML file.

The surprises are 100.4 and 100.9, which we might expect to round to 100.40000000000001 and 100.90000000000001 in the XML file.

I suspect that the reason they do not has to do with "banker's rounding" (round half to even) v. "normal" rounding (round half away from zero).  But that is only an educated guess at this point.  And probably TMI.

Answer 3

Thanks a lot! I understand the technical background now.

Answer 4

Hi Sheen,

thanks for the quick reply!

My Excel version is 15.38 (170902) – the latest, I think.

Sorry for the misunderstanding concerning the worksheet version, I wasn't very clear. I do use the .xlsx Format, but save the file as "Excel 2004 XML Worksheet (.xml)" in the end in order to get XML data.

Here are the simplest steps to reproduce the issue:

• open a new document
• insert test data — at least one number: 68.40 — the cell may be "Standard" or "Number", it doesn't seem to make a difference
• now save this data as"Excel 2004 XML Worksheet (.xml)"
• open the XML file

Result (snippet):

   <Row>

    <Cell><Data ss:Type="Number">68.400000000000006</Data></Cell>

   </Row>

Answer 5

Hi Michael,

Thanks for letting us know regarding this behavior. Can you please share your Excel version, located under Excel > About Excel.

Additionally, I'd like to know if this only occurs on Excel 2004 worksheets. Please try to save it to the .xlsx format and see if it works.

Finally, I'd like to know the steps you did to export the Excel sheet. If you are using a third-party application, then you might need to contact the developer to resolve this.

Regards,

Sheen