Issue with calculation of basic formula (i.e. x-y+z)

[norm.tasevski]

Hi,

I found an error in the way Excel calculates a basic formula (x+y-z). The
problem occurs when adding/subtracting compound fractions.

Example: 512.69 - 517 + 4.31 = 5.41789E-14 (answer should be 0). The problem
continues as the number increases (513.69 - x + y, 514.69 - x + y, etc)

The problem is not restricted to numbers past 512. The error depends on the
complexity of the compound fraction. In some cases, the error starts at 1
(e.g. 1.533 - 6 + 4.345 = -0.122000000000001), and the error "changes" at
certain points (e.g. 16.533 - 21 + 4.345 = -0.1219999999999990)

The errors also seem to correspond to a pattern. The problems seem to happen
when one of the compound fractions is an exponent of 2 (1, 2, 4, 8, etc).

I tested this on multiple versions of Excel (2003 for PCs and 2008 for
Macs), and the error is consistent across versions. I also used different
computers just to test if it was my version, and the error surfaces
regardless of computer used.

Though I haven't tested it, the problem may surface with other variants of
the same algebraic equations (e.g. x+y-z).

This has potential major implications. In my case, a budget sheet I had been
using was posting errors that was affecting cheques that we were issuing.

I am happy to hear feedback on this.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...af5d7a9&dg=microsoft.public.excel.crashesgpfs

 

[Charles Williams]

This behaviour is by design: Excel uses IEEE double precision floating point
binary arithmetic.

There are often no exact equivalent in binary of decimal numbers, just as
there is no exact equivalent in decimal arithmetic of the fraction 1/3

See http://www.cpearson.com/excel/rounding.htm for more information and
suggestions on what you might want to do about it.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Gary''s Student]

This is simple round-off error. If precise answers are required, then use
the =ROUND() function.

 

[Jerry W. Lewis]

To elaborate on the previous replies, the math is right, but Excel used
unavoidable approximations to your input numbers.

Most computer hardware and software (including Excel) use IEEE double
precision, which represents numbers in binary with effectively 53 bits for
the mantissa. Most terminating decimal fractions (including .69 and .31) are
non-terminating binary fractions that can only be approximated in binary.
The IEEE approximation to your numbers are
512.69000000000005456968210637569427490234375
-517
4.30999999999999960920149533194489777088165283203125E0
If you do the math, the correct result of your calculation starting from
these approximations to your input numbers is
5.417888360170763917267322540283203125E-14
which Excel correctly displays to its documented display limit of 15 decimal
digits.

Jerry

 

[Shane Devenshire]

Hi,

Computers work in binary, we work in decimals

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

If this helps, please cliick the Yes button.

Cheers,
Shane Devenshire