Subtracting one 2-decimal point number from another 2-decimal point number returns an eleven decimal point number. Why and how do I fix it?
example:
Cell B24=TRUNC(SUM(B22:B23),2) (value =134,388.33)
Cell C24=TRUNC(SUM(D22:D23),2) (value = 132,257.12)
Cell C25=B24-D24 (value = 2131.209999999990)
Why is the result to 11 decimal places when the two inputs are truncated at 2 decimal?
This is a well-known artifact of binary floating point arithmetic. There is no "fix" for this. As programmers, we can only cope with it. Most coping strategies in Excel revolve around rounding functions -- maybe =ROUND(B24-D24,2). Even then, you need to be aware that most terminating decimal fractions cannot be exactly represented in binary, so the problem can still be there.
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks