Title: Microsoft Excel 2000 and 2003 Faults, Problems, Workarounds and Fixes

Content: This project attempts to consolidate most of the criticisms, reported errors and faults in Excel's statistical applications, and to evaluate their claims; it also extends the analysis to additional functions and routines in both Excel 2000 and 2003 versions as of February 2005. More extensive testing was done on many functions and routines to discover regions of the parameter space where the functions and routines would return erroneous values, or return error codes.

The other purpose is to describe workarounds and fixes that overcome these faults and deficiencies in Excel-2000. Problems, faults and errors that still remain in Excel-2003 are also discussed. If the problem in Excel-2000 has been fixed in Excel-2003, it will be discussed. If there is no explicit indication of a change in Excel-2003, then it can be assumed that the problem still occurs in Excel-2003.

In addition, some more intensive investigations were made on some routines and functions to identify “hidden” properties or computation limits. Further, some of Microsoft’s Knowledge Base Articles (KBA’s) relevant to the functions and routines are also evaluated.

The project comprises 13 main papers (sections), and 26 separate notes that expand on some issues or areas of concern:

Section 1: Introduction. A general review on the use of Excel in teaching introductory statistics and for general data analysis.

Section 2: General problems with Excel. Introduces ideas about the meaning and use of problem, fault, defect and error terms, as they relate to the user and to the programmer (software developer).

Section 3: Excel computation and display issues. Gives a road map on how things occur in Excel. Describes the IEEE-754 standard and its limitations. Differences between exact mathematical equation results and the implementation in Excel. Describes the difficulties of linguistic translations to Excel inputs. Describes the problems of using the display as criteria for accuracy.

Section 4: The testing program for accuracy. Describes the basic methods used to test Excel outputs for accuracy. The STRD data sets. Discusses issues regarding the ability to obtain test data sets and precise output values. In many cases there is no agreed on computational method. Describes accuracy rating methods.

Section 5: Univariate analysis. Tests on the 22 Excel univariate (or descriptive statistics) functions.

Section 6: Analysis of variance (ANOVA). Tests on the ANOVA routines.

Section 7: Covariance and correlation. Tests on the covariance and correlation functions. Section 8: Linear and polynomial regression. Reviews the problems with Excel 2000 regression, the improvements in Excel 2003, and the remaining deficiencies.

Section 9: Nonlinear regression. Lists Previous tests on non-linear equation fitting using Solver. Solver basic deficiencies are discussed. Comparisons to other software products are made.

Section 10: Statistical distributions and related functions. A general description of the distribution functions available in Excel.

Section 11: Testing for accuracy and reliability of statistical distributions. Descriptions and methods on testing these distributions.

Section 12: Results of new tests on statistical distributions. Discrete, continuous density, continuous cumulative, and continuous inverse are covered in four subsections.

Section 13: Statistical tests, tests of significance and tests of a hypothesis. Tests on the t test, F test and Z test functions and routines. Discusses the problems and reported faults with these. Discusses the Fisher-Berens problem and Excel’s implentation of a solution.

Section 14: Random number generation. Discusses the Excel random number generators for both 2000 and 2003 and gives the results of tests.

Section 15: Add-in packages. PHSTAT1, PHSTAT2, DDXL and MEGASTAT were evaluated. Only MEGASTAT is acceptable.

Section 16: Bibliography

There are 26 notes that expand on parts of the testing project. They are referred to in the sections. An expanded XLS file is included that gives worksheets on how to easily generate the charts commonly found in introductory statistics textbooks.

The documents are available at www.daheiser.info

Website: http://www.daheiser.info
Contact Information: David Heiser, MS.
Carmichael, California