Inconsistent visualization of numerical values in

Question

I am attempting to read an Excel 2007 file (xlsx) from outside of Excel and I am finding an inconsistency that I cannot explain.

If you enter the value of 19.99 into a cell and then look at the underlying Xml document it is actually stored as 19.989999999999998. This is not the only value that does this, but it is a reasonable example. No formatting is applied in the sheet. In my example I just open a new Workbook, type in 19.99 in A1 and save the file.

I have attempted to open this simple example in both open office and Google docs and it shows 19.99 when the document is loaded.

My question is, how do I determine when to transform this value from 19.989999999999998 into 19.99 for use in other systems?

Answer 1

The variation between the 19.99 you entered and the 19.989999999999998 stored is the floating point variation... there will typically always be a slight discrepancy between the binary representation of a float (used internally by Excel) and the decimal used for display (and storage in the xlsx file).

Even if you haven't explicitly assigned a format to the cells, Excel applies a default formatting of "@" or "General", which typically (for numerics) displays to 2dp, applying scientific if needed. If you look at the number formatting for that cell (whether using theMS Excel front-end, or by examining the xlsx file), you should find that it is actually set to the default.