Xavier Olive research teaching python blog til cli

IEEE 754 – floating-point arithmetic in real life

4 December 2017

A funny experience with spreadsheets happened to us recently.

As part of some associative work, we use an Excel file provided by some third-party that sponsors 60% of some kind of spendings.

There is a cell in the provided file with the following formula:

= ROUNDDOWN(E6 * F6, 2)

So last time we had to ask for money, we entered the amount (493.5) in E6, checked F6 (60%), printed the document, and mailed it to the proper office.

It came back by email:

“Thank you for the document. We sponsor 60% of […]. It appears you made a mistake since you asked for 296.09 whereas you should have asked for 296.10. Please send a corrected version of the document”

Mmmh… What happened???

We check on the computer that emitted the document: 296.09. On another computer with a different spreadsheet software: 296.10…

Then I checked without the ROUNDDOWN operator on the “faulty” device: 296.10.

Then I have my idea:

>>> float.hex(493.5 * .6)

>>> float.hex(296.1)

And we are back to the 0.1 + 0.2 is not 0.3 issue!!

So now, I got the explanation:

  • the closest base-10 floating number to 0x1.2819999999999p+8 is 0x1.281999999999ap+8.
  • since we had to round the number up to get 296.1, the spreadsheet software checked for the closest base-10 floating number below 296.1, then rounded it down to two decimals and got 296.09.

Well, that’s obviously a bug which has been corrected in newer version of the software, but it was fun to search for what happened. Spreadsheets users are no computer scientists but they manipulate floating-point numbers!

The fun part was that the person in charge is rather computer illiterate and wanted to explain the sponsors she did nothing wrong, she just entered the proper number at the proper place!

I am not sure she was interested in my explanations about floating point representation issues and gave up quickly…