 Excel / Floating point rounding

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.

When comparing floats in older, er, other languages, I got used to writing this type of expression for equality:

abs(numOne - numTwo) < 0.001

You can vary the number based on the precision you want. Probably would work in Excel too.

This is interesting. I can duplicate your problem. I have not seen this before, but I would be interested in hearing about any solutions that come up.

In Excel if you want to round to a whole number you enter 0 for the number of digits.

=Round(A3,0)

I found that adding the rounding in here can fix your if statement:

=IF(ROUND((8.95-8),2)=0.95,TRUE,FALSE)

check you the Precision as Displayed option in Excel:

Thanks for posting!
Nate

1 Like

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.

1 Like

A1 - A2 = 0.95 ???
The Excel issue is the subtraction