Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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 -- Brevity is the soul of wit. "Clarkinson" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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 "Clarkinson" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
Thanks for that info. More than I know.
Dave -- Brevity is the soul of wit. "Jerry W. Lewis" wrote: 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 "Clarkinson" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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. "Clarkinson" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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. "Clarkinson" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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. "Clarkinson" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel errors ??
Very helpful, Jerry. Thank you.
"Jerry W. Lewis" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
spelling errors in excel | Excel Discussion (Misc queries) | |||
Is this a known excel error?? Sequential drag down & IF ERRORS?? | Excel Worksheet Functions | |||
excel errors | Excel Worksheet Functions | |||
how come one paricular excel file will not open w/o errors? | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |