Excel errors ??

[Clarkinson]

Hi,
We had this guy in the office today making out that it was easy to find
faults in Computer software.
He claimed it was easy to show and prove that Excel calculates eroneously.
He didn't specify how or under what cirmstances and didn't have time to do a
demonstration.

Anyone else heard of this?

I check "Excel makes mistakes" on Google and found a few articles dated 2003
dealing with a statistical covariance problem .

Apart from that, nothing much. Is there something I've been missing ?

TIA,
EJC

 

[Guest]

A number of potential issues:

1) Precision. Excel only handles 15 digits of precision. Bad for
engineers, physicists, astronomers, certain statistical applications, etc.
Irrelevant for accountants, financial analysts, etc.

2) Statistical analysis: various academic studies have been conducted by
statisticians alleging that the way Excel calculates various statistical
analysis techniques is either erroneous or not robust or both. I don't
recall enough to give detail on this subject, however, if this is the issue
about which you are concerned, there is (a) plenty of newsgroup discussion on
this topic, and (b) many papers available on the web.

3) Human errors made in complex spreadsheets. Essentially, complex
spreadsheets, typically financial models, in which formulas propagate over a
series of workbook tabs can quickly become junk if either the assumptions
made or bad, or logical and/or mathematical errors are built into the system.

Hope that helps.

Dave

 

[Guest]

Excel's basic arithmetic, trig functions, etc. appear to be handled by the
CPU, as you would expect since otherwise MS would have re-invented the wheel.
As such, the math is no better or worse than any other software package
(excepting arbitrary precision packages like Maple and Mathematica). The
internal representation of numbers follows the IEEE 754 standard for double
precision binary storage; again no better or worse than any other standard
program.

Where calculations were not available in the CPU (probability, statistics,
engineering functions [like Bessel and erf functions], etc. then the picture
changes. MS made a number of poor algorithm choices.

Prior to 2003, probability functions were generally reliable to about 6
decimal places (not 6 significant figures), provided they returned a value at
all. For simple statistical tests, that is usually adequate, and better than
you would find in most printed tables. The real criticism was that the
working range was so limited that you often couldn't get results for real
world problems, although it was fine for textbook examples. In Excel 2003,
MS "improved" their algorithms for discrete probability distributions, with
the result that they now have a much larger range where they return values,
but the value that they return may be totally wrong! For probability
calculations, download Ian Smith's library of VBA functions from
http://members.aol.com/iandjmsmith/examples.xls

Excel's engineering functions have modest accuracy and severe limitations on
their working range. Again, they may be adequate for (some) textbook
examples, but not for real-world applications.

Statistical calculations prior to 2003 were mathematically correct but
numerically unstable, meaning that they could fail for numerically difficult
problems. Excel 2003 uses algorithms that are quite good, with the exception
of a bug in LINEST such that coefficient estimates that are exactly zero are
not to be trusted (fixed in 2007). Oddly, the polynomial trendline for XY
(Scatter) charts has always used an excellent algorithm that is comparable
(and in some surprising cases much better) in accuracy than main-stream
statistics packages. However, Excel is not (and likely never will be) a
statistics package; if you need to do serious statistical work (or even a lot
of simple statistical work), and you don't have SAS, JMP, or other
main-stream statistics package, then you may be better served to download and
learn R
www.r-project.org

The biggest question with Excel, like any software package, is how you know
that you have actually asked it to do what you intended (validation).
Several papers have been published about the rate of user errors in
spreadsheets. It is not clear to me that the problem is inherently greater
for spreadsheets; it is just that they are so easy to use that they have many
users who have never considered the issue.

Jerry

 

[Guest]

Thanks for that info. More than I know.

Dave
--
Brevity is the soul of wit.

Excel's basic arithmetic, trig functions, etc. appear to be handled by the
CPU, as you would expect since otherwise MS would have re-invented the wheel.
As such, the math is no better or worse than any other software package
(excepting arbitrary precision packages like Maple and Mathematica). The
internal representation of numbers follows the IEEE 754 standard for double
precision binary storage; again no better or worse than any other standard
program.

Where calculations were not available in the CPU (probability, statistics,
engineering functions [like Bessel and erf functions], etc. then the picture
changes. MS made a number of poor algorithm choices.

Prior to 2003, probability functions were generally reliable to about 6
decimal places (not 6 significant figures), provided they returned a value at
all. For simple statistical tests, that is usually adequate, and better than
you would find in most printed tables. The real criticism was that the
working range was so limited that you often couldn't get results for real
world problems, although it was fine for textbook examples. In Excel 2003,
MS "improved" their algorithms for discrete probability distributions, with
the result that they now have a much larger range where they return values,
but the value that they return may be totally wrong! For probability
calculations, download Ian Smith's library of VBA functions from
http://members.aol.com/iandjmsmith/examples.xls

Excel's engineering functions have modest accuracy and severe limitations on
their working range. Again, they may be adequate for (some) textbook
examples, but not for real-world applications.

Statistical calculations prior to 2003 were mathematically correct but
numerically unstable, meaning that they could fail for numerically difficult
problems. Excel 2003 uses algorithms that are quite good, with the exception
of a bug in LINEST such that coefficient estimates that are exactly zero are
not to be trusted (fixed in 2007). Oddly, the polynomial trendline for XY
(Scatter) charts has always used an excellent algorithm that is comparable
(and in some surprising cases much better) in accuracy than main-stream
statistics packages. However, Excel is not (and likely never will be) a
statistics package; if you need to do serious statistical work (or even a lot
of simple statistical work), and you don't have SAS, JMP, or other
main-stream statistics package, then you may be better served to download and
learn R
www.r-project.org

The biggest question with Excel, like any software package, is how you know
that you have actually asked it to do what you intended (validation).
Several papers have been published about the rate of user errors in
spreadsheets. It is not clear to me that the problem is inherently greater
for spreadsheets; it is just that they are so easy to use that they have many
users who have never considered the issue.

Jerry

Hi,
We had this guy in the office today making out that it was easy to find
faults in Computer software.
He claimed it was easy to show and prove that Excel calculates eroneously.
He didn't specify how or under what cirmstances and didn't have time to do a
demonstration.

Anyone else heard of this?

I check "Excel makes mistakes" on Google and found a few articles dated 2003
dealing with a statistical covariance problem .

Apart from that, nothing much. Is there something I've been missing ?

TIA,
EJC

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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/microsoft.public.excel/msg/b106871cf92f8465 ).
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

 

[Guest]

Very helpful, Jerry. Thank you.

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/microsoft.public.excel/msg/b106871cf92f8465 ).
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

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?