Round function returns different values for same calculated input.

(A worksheet illustrating my issue is attached.)

I've got 3 formulas that calculate to the same value (-0.05)

= 58.1 - 58.15
= 58.4 - 58.45
= 64.4 - 64.45

Why, then, does the round function return different values?

=ROUND(58-58.05,1) returns 0
=ROUND(58.4-58.45,1) returns -0.1
=ROUND(64.4-64.45,1) returns 0

Is there some logic at work here? (Maybe I'm searching the wrong keywords, but I'm stumped.) Thanks.

Binary floating point can't represent base 10 decimals precisely, so you get rounding errors, especially when you subtract.

With the Double expressed in hex, -0.05 is BFA999999999999A; note that it's a repeating fraction (like 0.3333...) is in decimal.

The results of your subtractions are

Please Login or Register  to view this content.

Hi Kris,
Read http://www.cpearson.com/excel/rounding.htm then open you example workbook and
click on F10 and find the Formula Evaluate Tool. Step through the evaluate tool and see how it deals with F10. Then do the same with F11 and F12.

You will see that the internal representation of F10 and F12 are .4999999999972
But in F11 the internal representation is .500000000043

Then read the last section of http://office.microsoft.com/en-us/ex...010070518.aspx

hope that also explains why .05 is not equal to .05 .