Round function (worksheet one) in Excel

Question

Using Excel 2013, typing this into two cells yields different results.

=ROUND((10.45-10.00)/1,1) = 0.4

but

=ROUND(0.45/1,1) = 0.5

And I have no idea why this is the case. Anyone have an explanation?

Answer 1

It looks like the top calculation is creating an infinitesimally small difference. To see this, pull the formula out of the ROUND function. Using the "Increase Decimal" button, expand both values to 15+ decimal places. You'll see that the top value is actually 0.449999999999999.

I have no idea why it actually does this.

Answer 2

Because Excel uses floating-point math, so results are not guaranteed to be exact. On my Excel 2013 installation, if I enter =10.45-10.00 in a cell, then change that cell to Number with 30 decimal places of precision, the actual answer is 0.449999999999999000000000000000. Since 0.44999... and 0.45 are on opposite sides of the rounding boundary, they round to one decimal place differently.

Edit See also this answer and the question and other answers, which go into much more detail about why Excel's math is inexact.