When IEEE 754 calls tell him I want that digit back

You learn something new every day -- Then you can go home.

A guy at work was telling me about an Excel problem he was having. He was trying to put 020500609343470004 into a cell and it wasn't displaying and it was coming out as 20500609343470000. Note that last digit. Excel was changing the 4 into a 0. Why was this happening? (He apparently wasn't concerned about the leading zero). I gave the quick fix answer: format the cell as text or put a single-quote at the front of the input. This solved the immediate problem and he walked away happy.

But I still wanted to know what was going on. Always looking for an opportunity to learn something. After just a bit of searching on Google, I learned that Excel (and just about every program and operating system out there) uses the IEEE 754 specification for displaying numbers. That spec strikes a good balance between accuracy and speed. However, one of the side effects of this is that it only cares about 15 significant digits. Looking at the number above there are certainly more than that, so the 4 at the end just doesn't count.

Fun, right? I knew you'd like that.

References:

• Microsoft KB article 78113
• Mircosoft KB article 214118
• For increased precision in Excel: xlPrecision