Numeric precision issue in Excel 2007 when saving as XML

Question

I am encountering a strange issue when saving documents in XML Spreadsheet 2003 format using Excel 2007.

It seems to randomly change numeric values such as "0.58" to "0.57999999999999996".

What's really odd is that you CANNOT see this issue when you are in Excel. You can only see this when viewing the XML data directly from a text editor.

Has anyone encountered this before? If so, is there any sort of fix or workaround?

Answer 1

Excel stores numeric data as floating point. The number to the right of the decimal place (the fractional portion) is only approximate. There is no work around, 0.58 cannot be represented as a floating point number that is exactly 0.58.

When loading the XML file at that point you should convert the number back to floating point or better yet a fixed digit decimal class (eg. Decimal in C#).

Answer 2

The key is that Excel stores numbers as base 2 floating point numbers. Most decimal fractions cannot be represented as base 2 floating point numbers.

Some answers have stated that "0.58 cannot be represented by floating point". This is not correct since 0.58 can be represented by base 10 floating point numeric types such as the .NET Decimal type - of course this does you no good since you cannot change the underlying type used for numbers in Excel.

Excel always displays numbers in the formula bar rounded to 15 significant digits of precision. So, this number shows up as 0.58 in the formula bar even though it is not "exactly" 0.58 - and cannot be exactly 0.58 because of the limitations of Excel's underlying numeric type.

You might try turning on Precision As Displayed (in Excel 2007 this is found under Excel Options -> Advanced -> When calculating this workbook: -> Set precision as displayed) - it may or may not cause Excel to round this particular number when saving to XML, but it would at least give you more consistent results. Just be careful since Precision As Displayed will round numbers already stored in numeric cells (it is not undoable), as well as rounding the results of calculations (this part is undoable since turning it off will recalculate without rounding). Precision as displayed will round based on the number format, so a number format of "0.00" will cause it to round to two decimal places - but most decimal fractions are not representable by Excel's base 2 number system so 0.58 might still get you the same thing when written to XML. Numbers formatted as "General" will be rounded to 15 significant digits of precision with Precision As Displayed turned on.