IEEE 754 – floating-point arithmetic in real life
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)
'0x1.2819999999999p+8'
>>> float.hex(296.1)
'0x1.281999999999ap+8'
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+8is0x1.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 below296.1, then rounded it down to two decimals and got296.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…
