Excel 2003 calculation problem

[Jenny]

Dear all,

version : excel 2003

I just want to a simple minus calculation for all cells in column C using
the formula A1-B2. After that , some of my cells content in column C differ
many decimal places.For example , if A1 = 0.1 , B1 = 0.05 , then after
A1-B1 , C1 = 0.0499999999999998 . I want C1 to be exactly 0.05 , what can I
do ??

Thanks !!

 

[Bob Phillips]

=round(A1-B2,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bernard Liengme]

For an explanation of this rounding 'error', see
http://docs.sun.com/source/806-3568/ncg_goldberg.html
best wishes

 

[Guest]

Most decimal fractions (including .1 and .05) have no exact binary
representation and hence must be approximated in binary (used by Excel and
almost all other computer software) much as 1/3 can only be approximated in
decimal. In depth detail is given at Bernard's link.

Excel's answer is exactly correct given the initial approximations to your
inputs, and agrees with results that you would get from almost all computer
software.

Since you know that you are just adding/subtracting numbers with no more
than 2 decimal places, Bob's suggestion of rounding to 2 places merely
removes the evidence of these initial approximations without doing violence
to the calculation.

Jerry