Rounding Error when adding or subtracting two cells

[mtheo]

Hi all,

this comment refers to a rounding error I just happened to encounter.
Try adding the following two numbers: 109563.24 and -105537.00. This should
normally give you 4026.24 (and it does!!!).

However if you display more decimals for the amount you will notice that the
11th decimal is wrong (it has a value of 1 instead of 0). Big deal one might
say.

However if you try to use this value in an IF function (e.g. IF Cell =
4026.24) this will return FALSE. Has anyone else noticed any similar cases?
Is this a known Excel bug? Note that the same has been observed both in Excel
XP and Excel 2003.

A similar error has also been observed when adding the following pairs of
numbers:

109441.87 and -105282.58
111058.21 and -105351.20
110761.07 and -105388.00
10669408 and -105943.00

There seems to be a pattern that this happens when we add (subtract) numbers
that are in this range (although I have not proven it or investigated any
further).

Any comments?
Markos

 

[Bob Phillips]

This is due to floating point arithmetic and arithmetic precision. Do a
google on posts by Jerry Lewis on that subject, he will expolain it far
better than I.

To resolve it, try

Round(Cell,2) = 4026.24

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)