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

beats by dre cyber Monday jordan 13 grey toe jordan 11 legend blue hollister black friday north face cyber monday uggs black friday lululemon black friday hollister black friday jordan 6 black infrared michael kors cyber monday north face cyber monday coach black friday north face black friday michael kors black friday uggs black friday coach outlet online hollister black friday north face cyber monday beats by dre black friday black infrared 23 13s jordan 13 grey toe coach black friday north face cyber monday michael kors outlet coach black friday lululemon cyber monday uggs black friday uggs cyber monday north face outlet lululemon black friday lululemon cyber monday michael kors cyber monday beats by dre cyber monday uggs cyber monday michael kors black friday hollister cyber monday jordan 6 black infrared uggs cyber monday jordan 13 grey toe north face black friday coach black friday michael kors cyber monday uggs black friday michael kors cyber monday uggs cyber monday lululemon black friday black infrared 6s lululemon black friday grey toe 13s jordan 11 legend blue jordan 11 legend blue michael kors black friday michael kors cyber monday uggs black friday coach black friday north face outlet black infrared 6s jordan 13 bred north face black friday coach cyber monday