DESCRIPTION

THIS WEBSITE IS THE PROCESS OF BEING REWRITTEN. The June 2008 issue of Computational Statistics and Data Analysis covered an analysis of Excel 2007. These faults and errors are being reviewed for inclusion.

Section 20 has been rewritten with new material based on errors pointed out by Paulo Tanimoto.

The section on RAND is not yet finished. B.D. McCullough has reported some uncertainties about RAND which are to be investigation.

Section 2 was expanded to cover the faults in Excel's "Order of Precedence"

SCOPE

If you have any comments, or have noted some errors or faults with Excel or my findings, please send it by email to me at "d_heiser@att.net".

CONTENTS

I. Introduction (update, 12/9/08)

II. General Problems With Excel (update, 9/13/09)

III. Excel Computation and Display Issues (update, 9/30/07)

IV. The Testing Program For Accuracy (update, 11/27/07)

V. Univariate Analysis (update, 3/18/08)

VI. Analysis of Variance (ANOVA) (update, 8/28/07)

VII. Relationships Between X-Y Typs of Data Sets, 12/10/08

VIII. Covariance and Correlation, 12/10/08

IX. Linear Regression, 12/16/08

X. Non-linear Regression, 12/10/08

XI. Chart Trendline Regression, 1/2/09

XII. Forecast, 12/7/08

XIII. What-If Solution Tools, 12/7/08

XIV. Statistical Distributions and Related Functions (update, 8/28/07)

XV. Testing for Accuracy and Reliability of Statistical Distributions (update, 8/26/07)

XVI-1. Results of New Tests on Statistical Distributions, Discretes (update, 8/28/07)

XVI-2. Results of New Tests on Statistical Distributions, Continuous Functions (update, 8/28/07)

XVI-3. Results of New Tests on Statistical Distributions, Continuous Cumulative (update, 8/26/07)

XVI-4. Results of New Tests on Statistical Distributions, Comtinuous Inverse (update, 8/26/07)

XVII. Statistical Tests, Tests of Significance and Tests of a Hypothesis (update, 8/28/07)

XVIII. Random Number Generation (update, 12/10/08)

XIX. The Data Analysis Tool Routines (update, 12/15/08)

XX. Graphics, Charts and Visual Displays (update 06/27/09)

XXI. Add-In Programs, Functions and Routines (unfinished)

XXII. Bibliography (updated, 9/13/09)

Sample Excel 2003 Files

NOTES (Revised 6/25/08)

Note A: Comments On Teaching/Using Excel

Note B: Excel Versions and Sources

Note C: Microsoft Knowledge Base Articles (KBA's)

Note D: Excel Help From The Internet

Note E: Guide To Excel Statistical Functions, Routines and Tools

Note F: Help Screen Errors

Note G: Data Input Errors

Note H: Some Specific Lists Of Excel Faults

Note I: Improving Documentation

Note J: Ordinal, Nominal and Likert Scale Variables

Note K: New Display Modified Probability Distributions

Note L: Autocorrelation

Note M: An Actual Problem Requiring Unbiased Standard Deviations

Note N: Ranking, Quartiles, Medians and Percentiles

Note O: Averages, Standard Deviations and Pre-centering

Note P: Alternate Algorithms

Note Q: Data Entry For ANOVAs In The Data Analysis Tool Pac

Note R: Linear Regression

Note S: Linear Regression Throught The Origin, Excel 2000

Note T: Singularity, Multi-colinearity, Accuracy And Other Matrix Problems

Note U: Polynomial Regression

Note V: Regression Normal Probability Plot

Note W: Standardized Residuals

Note X: Support In Excel For Tests Of Significance

Note Y: Constructing A Hypothesis

Note Z: Setting Up The Excel Sheet For Calculating P Values

Note AA: Generate Diehard Test Input Files

Note AB: Diehard-II Output For RAND, Excel 2000

Note AC: Marsaglia's MWC256 RNG

Note AD: Diehard-II And -III Output For RAND, Excel 2003

Note AE: Random Number Generator VBA Routines

Note AF: The Wilkinson-Sawitski Series Of Tests On Excel 2007

Note XN: XNUMBERS, A Multi-precision Floating Point Calculus For Excel ACKNOWLEDGEMENTS

I would like to thank Dr. B.D. McCullough, Department of Decisions Sciences and Department of Economics, Drexel University, Philadelphia, PA. for his help in making comments and corrections to the paper. I have tried to incorporate all his comments and corrections, and the current versions are the result.

I would also like to thank Jerry W. Lewis, for his assistance in tests on statistical distributions. He also steered me to Smith's set of accurate probability distributions.

I thank the Journal "Computational Statistics and Data Analysis" (Elsevier) for including a short note in the November 2006 issue on this website.

Thanks to Leonardo Volpi for information on some problems in Excel, on his XNUMBERS extended precision add-in and the optimization and non-linear equation add-ins.

Sawilowsky's "Journal of Modern Applied Statistical Methods" has been a good source on articles that directly relate to the problems of statistical analysis.

Also to those who sent in comments and corrections to my text.

For the 2007 version thanks to:

Christopher L. Paras on the chart output graph fault in the Data Analysis Tool Pac Regression routine,

Joseph Billo 10/2/07 on different ways to improve the Solver fit to non-linear equations.

Bruce R. Hargreaves 11/20/08 on Trendline faults.

Christian Asseberg 9/2/09 on order of precedence issues.

COMMENTS AND REMARKS

This effort to test Excel started in 2001, based on McCullough's articles in "The American Statistician" in 1998, and in suceeding articles in other publications.

This URL also includes material on the free Excel add-ins from the "Foxes" for optimization and non-linear equation fits. Also included is the extended precision computation package (xnumbers) for Excel. These extend the basic Excel computational capabilities.

CORRECTIONS AND CHANGES

Sections 1 through 21 are complete rewrites of the first edition. Most of the structure, text, tables and figures are the same as the first edition. I currently have both the Excel 2003 and 2007 versions loaded on my computer, so I can check out comments from viewers on either version. There are differences between Excel 2003 and Excel 2007.

CORRECTIONS AND CHANGES TO THE THIRD EDITION:

Stuart MacDonald on 6/3/07 pointed out a numerical error on page 4 of note AA regarding XNUMBERS. The correction was made both in Note AA and on Volpi's XNUMBERS Website. June 6, 2007.

Correction to Bibleography and Section 18 reference, Harold Henderson, 1/17/08.

Numerical errors in table 5.5, was noted by Michelle Chutka, 3/18/08. The errors have been corrected.