Rounding Error when adding or subtracting two cells
 

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

Rounding Error when adding or subtracting two cells
 

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)

"mtheo" wrote in message
...
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

Rounding Error when adding or subtracting two cells
 

Excel (and almost all software) does binary math. In binary most terminating
decimal fractions (including all of the ones in your examples) are
non-terminating binary fractions that must be approximated.

You do not see the approximations directly, because Excel will not display
more than 15 digits (documented in Help), but they are there. For example,
when you enter 109563.24, what you get is
109563.240000000005238689482212066650390625. When you subtract off 105537,
you get 4026.240000000005238689482212066650390625, which to 15 figures
displays as 4026.24000000001.

The math is right, given the unavoidable approximations to the inputs. You
would get a similar phenomenon in finite precision decimal arithmetic from
(2/3+2/3)-1, since 2/3 has to be approximated in decimal.

I have provided VBA functions at
http://groups.google.com/group/micro...06871cf92f8465
to help you explore the mysteries of binary representation of numbers and
their impact on decimal arithmetic.

Your last example involves the difference between two integers, which
involves no approximation; hence Excel returns the exact solution. Perhaps
you meant 106694.08 instead of 10669408?

Jerry

"mtheo" wrote:

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