This phenomenon is often called "catastrophic cancellation" by computer
scientists. A google search on that term gives over 9,000 hits.
Excel and almost all other general-purpose numerical software packages use
IEEE double precision, which gives 53-bit (roughly 15 decimal digit)
accuracy. Intel math coprocessors internally use 64-bit (roughly 18 decimal
digit) accuracy, and some compilers give you access to that extra precision.
Some compilers support quadruple precision which gives 113-bit (roughly 33
decimal digit) accuracy.
Some special purpose math programs allow you specify the desired precision
(using techniques similar to those employed in
http://groups.google.com/group/micro...06871cf92f8465 ).
But these approaches result in relatively slow calculations, and merely
postpone finite precision issues instead of eliminating them altogether.
A very few special purpose math programs (such as Maple and Mathematica) can
do symbolic algebra. Using that facility, you can have infinite precision
calculations, but I know of no other means to avoid these issues.
A simple way to calibrate your expectations (which works well regardless of
whether the underlying math is binary or decimal), is to think of any
computer number as having unknown digits beyond the 15th figure. Thus for
your problem (which I presume to have been 1+x-1 instead of 1+x-x) you have
1.00000000000000???????????
0.0000000100000000000000??
------------------------------------
1.00000001000000???????????
-1.00000000000000???????????
------------------------------------
0.00000001000000???????????
which is in fact what Excel gave you.
If these facts of life in computer arithmetic cause problems in your
calculations, then you must restructure your calculations to either be more
robust to finite precision issues (such as testing for approximate equality
instead of exact equality) or else do the calculation in ways that tend to
avoid catastrophic cancellations (such as algebraic simplification
[recognizing that 1+x-1=x and therfore eliminating both operations], or
special programming techniques like
http://en.wikipedia.org/wiki/Kahan_summation_algorithm ).
These approaches tend to require understanding of the specific calculations
required, and hence are not something that Microsoft can do for you as a
general solution.
Jerry
"engineer" wrote:
Hi Jerry
Thanks for your reply, which is a useful correction to my remark.
I agree that this is a round-off issue. For example,
in computing 1+x-x with x=1E-8 I obtain 9.999999939225290E-09, which is in
error by only the last figure if I round off at 7 decimal places, or eight
figures. If I use x=1E-14, I get 9.9920072E-15, and at x=1E-15 I get zero. So
I conclude that due to round-off 1+x-1 will exhibit increasing inaccuracy as
x shrinks to values where 1+x to only 15 figures is simply 1. That results
in 8 figure accuracy in 1+x-1 for x in approximately the range 1E-8 to 1E-10.
I suppose the merit in calling round-off issues not an "error in basic
arithmetic", but a "finite precision issue" is that it means Excel is no
worse than other software with the same finite precision issues? Isn't there
other software, however, with different precision issues?
"Jerry W. Lewis" wrote:
You are not thinking in terms of finite precision mathematics. Consider
x=1/3/10^8. With 15 figure accuracy, your calculation would be
1.00000000000000
.00000000333333333333333
-----------------------------------
1.00000000333333
-1.00000000000000
-----------------------------------
0.00000000333333
The final result only caries 6 figures, not because any operation involved
less than 15 figures, but because most of those 15 figures were wasted on
accurate representation of the 1 in 1+x, with the result that most of the
accuracy for x was lost.
On top of the basic issues of finite mathemetics, you also must consider
that most terminating decimal fractions (such as 1E-1) are non-terminating
binary fractions that can only be approximated (just as 1/3 can only be
approximated as a decimal fraction), and hence you will see some of these
finite precision issues in "simple" calculations where you don't expect them
to be relevant.
I am not aware of any instance where Excel performs basic arithmetic
incorrectly.
Jerry
"engineer" wrote:
I don't know if you count it as an error exactly, but try calculating the
formula y= 1+x-1, which should result in the value x. If x is small, say
1E-8, and you display the result of the calculation to say 12 figures, you
will find the result is not x, but may be more or less than x depending on
the value chosen. Accuracy exists only to 8 sig figs, despite claims that
Excel has 15 figure accuracy.