Ran into an interesting issue with Excel’s handling of floating point numbers.
Take the following formula:
= IF((8.95-8) = .95, TRUE, FALSE)
You’d think that would return TRUE, but it does not.
If I enter 8.95 into cell A1, and 8 into cell A2, the formula =A2-A1 into cell A3, and =Round(A3,1) into cell A4, I get:
Shouldn’t 0.95 round up to 1? Cell A4 show 0.9.
If I change the format of those cells to Number (15 decimal places), it shows that 8.95 - 8 is not exactly 0.95
This became apparent when processing timeclock data. The timeclock system must have similar issues. Time is reported in hours, rounding to 10ths. From 8:00 to 8:57 is 57 minutes, which is exactly 0.95 hr, which should round up to 1.0 hours. But the timeclock system shows that as 0.9 hrs - like it was actual 0.9499999999 , which rounds to 0.9.
In my days of embedded programming on 8 bit processors, I would do something similar. I first ran across floating point imprecision when calculating statistics. To my surprise, 6 * 19.9 <> 19.9 + 19.9 + 19.9 + 19.9 + 19.9 + 19.9
edit
It was even worse than the values not being equal. I was trying to take the square root of a difference (which should never be able to be less than zero), and it turned out that 6 * 19.9 - (19.9 + 19.9 + 19.9 + 19.9 + 19.9 + 19.9) was less than zero, and threw a NEG SQRT ERR, when it should have been the square root of zero.
