Why in excel does 41202.80721 get changed to 41202.8072099999

J

Jamo12t

Why do certain values in excel get converted such as the above or 41141.51430.

Thanks

James
 
B

Bob I

To simplify it's caused by the limits of binary storage and decimal
representation and the conversions between the two.
 
J

Jerry W. Lewis

When you enter 41202.80721 into a cell in Excel versions prior to 2007, the
value in the formula bar shows as 41202.8072099999 as does its string
conversion or formats that show 15 figures. This is a bug in Excel’s display
routine that is fixed in Excel 2007. MS publicly acknowledged the bug for
only one of the millions of decimal fractions where it occurs.
http://support.microsoft.com/kb/161234
In all instances I am aware of, the underlying value retains the correct
binary representation, but the displayed value ends in 999… instead of
correctly rounding up the displayed value.

I cannot reproduce a problem by entering 41141.51430 in a cell. Likely that
value in your case is the result of calculations, which did not equal what
you expected because of accumulated discrepancies between binary
approximations and your intended decimal numbers. The binary thing is common
to almost all numeric software; Excel's math is correctly implemented; and
Excel 2007 will give the same result as earlier versions.

Jerry
 
Top