Excel 2003 calculation problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jenny - 15 Sep 2006 15:30 GMT

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 !!

Reply to this Message

Bob Phillips - 15 Sep 2006 15:55 GMT

=round(A1-B2,2)

Signature

HTH

Bob Phillips

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

> Dear all,
>
[quoted text clipped - 7 lines]
>
> Thanks !!

Reply to this Message

Bernard Liengme - 15 Sep 2006 20:26 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Dear all,
>
[quoted text clipped - 7 lines]
>
> Thanks !!

Reply to this Message

Jerry W. Lewis - 15 Sep 2006 22:22 GMT

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

> Dear all,
>
[quoted text clipped - 7 lines]
>
> Thanks !!

Reply to this Message

Rate this thread: