On the Numerical Accuracy of Spreadsheets

This paper discusses the numerical precision of five spreadsheets (Calc, Excel, Gnumeric, NeoOffice and Oleo) running on two hardware platforms (i386 and amd64) and on three operating systems (Windows Vista, Ubuntu Intrepid and Mac OS Leopard). The methodology consists of checking the number of correct significant digits returned by each spreadsheet when computing the sample mean, standard deviation, first-order autocorrelation, F statistic in ANOVA tests, linear and nonlinear regression and distribution functions. A discussion about the algorithms for pseudorandom number generation provided by these platforms is also conducted. We conclude that there is no safe choice among the spreadsheets here assessed: they all fail in nonlinear regression and they are not suited for Monte Carlo experiments.

[1]  Brian D. Ripley,et al.  Thoughts on pseudorandom number generators , 1990 .

[2]  Elaine B. Barker,et al.  A Statistical Test Suite for Random and Pseudorandom Number Generators for Cryptographic Applications , 2000 .

[3]  Richard M. Lindstrom,et al.  Ensuring accuracy in spreadsheet calculations , 1998 .

[4]  John C. Nash,et al.  Teaching statistics with Excel 2007 and other spreadsheets , 2008, Comput. Stat. Data Anal..

[5]  Jocelyn Kaiser,et al.  Software Glitch Threw Off Mortality Estimates , 2002, Science.

[6]  Leo Knüsel.  On the accuracy of the statistical distributions in GAUSS , 1995 .

[7]  Leo Knüsel,et al.  On the accuracy of statistical distributions in Microsoft Excel 97 , 1998 .

[8]  Bruce D. McCullough,et al.  On the accuracy of statistical procedures in Microsoft Excel 2000 and Excel XP , 2002 .

[9]  David A. Heiser,et al.  On the accuracy of statistical procedures in Microsoft Excel 2007 , 1999, Comput. Stat. Data Anal..

[10]  Xiao-Feng Xie,et al.  DEPSO: hybrid particle swarm with differential evolution operator , 2003, SMC'03 Conference Proceedings. 2003 IEEE International Conference on Systems, Man and Cybernetics. Conference Theme - System Security and Assurance (Cat. No.03CH37483).

[11]  日経BPソフトプレス,et al.  Microsoft Office Excel 2007 , 2003 .

[12]  S. E. Kruck,et al.  Testing spreadsheet accuracy theory , 2006, Inf. Softw. Technol..

[13]  Marcelo G. Almiron,et al.  The reliability of statistical functions in four software packages freely used in numerical computation , 2009 .

[14]  Brian D. Ripley,et al.  Stochastic Simulation , 2005 .

[15]  Charles H. Apigian,et al.  Is Microsoft Excel 2003 Ready for the Statistics Classroom? , 2005, J. Comput. Inf. Syst..

[16]  Bruce D. McCullough,et al.  Is it safe to assume that software is accurate , 2000 .

[17]  Albert Dahan,et al.  A comparative study of two methods for attaining constant alcohol levels. , 2008, British journal of clinical pharmacology.

[18]  Grenville J. Croll.  Spreadsheets and the Financial Collapse , 2009, ArXiv.

[19]  Jakob Roth,et al.  Ergebnisse von Qualitätskontrollen der individuellen Patientendosen in der Radioonkologie* , 2008, Strahlentherapie und Onkologie.

[20]  Xiao-Feng Xie,et al.  Social cognitive optimization for nonlinear programming problems , 2002, Proceedings. International Conference on Machine Learning and Cybernetics.

[21]  Nourdine Aliane.  Spreadsheet-based control system analysis and design [Focus on Education] , 2008 .

[22]  Donald Ervin Knuth,et al.  The Art of Computer Programming, Volume II: Seminumerical Algorithms , 1970 .

[23]  H. Vinod.  Review of GAUSS for Windows, including its numerical accuracy , 2000 .

[24]  Michael Murphy,et al.  Errors in patient specimen collection: application of statistical process control , 2008, Transfusion.

[25]  Micah Altman.  A Review of JMP 4.03 With Special Attention to its Numerical Accuracy , 2002 .

[26]  B. D. McCullough,et al.  Microsoft Excel's 'Not The Wichmann-Hill' random number generators , 2008, Comput. Stat. Data Anal..

[27]  B. D. McCullough,et al.  Special section on Microsoft Excel 2007 , 2008, Comput. Stat. Data Anal..

[28]  Takuji Nishimura,et al.  Mersenne twister: a 623-dimensionally equidistributed uniform pseudo-random number generator , 1998, TOMC.

[29]  L. Conte,et al.  Biological effective dose evaluation in gynaecological brachytherapy: LDR and HDR treatments, dependence on radiobiological parameters, and treatment optimisation , 2008, La radiologia medica.

[30]  Roger L. Berger.  Nonstandard operator precedence in Excel , 2007, Comput. Stat. Data Anal..

[31]  D. B. Davis,et al.  Sun Microsystems Inc. , 1993 .

[32]  Oscar H. Bustos,et al.  Statistical functions and procedures in IDL 5.6 and 6.0 , 2006, Comput. Stat. Data Anal..

[33]  John C. Nash,et al.  Spreadsheets in Statistical Practice—Another Look , 2006 .

[34]  Stefan Bäckman.  Microeconomics using EXCEL®: Integrating Economic Theory, Policy Analysis and Spreadsheet Modelling , 2008 .

[35]  A. T. Yalta.  The Numerical Reliability of GAUSS 8.0 , 2007 .

[36]  Stephen G. Powell,et al.  Impact of errors in operational spreadsheets , 2009, Decis. Support Syst..

[37]  A. Talha Yalta,et al.  GRETL 1.6.0 and its numerical accuracy , 2007 .

[38]  Robert J. Pavur,et al.  A comparative study of the reliability of nine statistical software packages , 2007, Comput. Stat. Data Anal..

[39]  James P. LeSage,et al.  Assessing the accuracy of ANOVA calculations in statistical software , 1989 .

[40]  A. Talha Yalta,et al.  The accuracy of statistical distributions in Microsoft® Excel 2007 , 2008, Comput. Stat. Data Anal..

[41]  I. D. Hill,et al.  Generating good pseudo-random numbers , 2006, Comput. Stat. Data Anal..

[42]  I. D. Hill,et al.  Correction: Algorithm AS 183: An Efficient and Portable Pseudo-Random Number Generator , 1982 .

[43]  Bruce D. McCullough,et al.  Assessing the Reliability of Statistical Software: Part I , 1998 .

[44]  Sait Ismail Ozkaya.  An EXCEL macro for importing log ASCII standard (LAS) files into EXCEL worksheets , 1996 .

[45]  Brian Gough,et al.  GNU Scientific Library Reference Manual - Third Edition , 2003 .

[46]  Yu-Sung Su,et al.  Computational Statistics and Data Analysis It's Easy to Produce Chartjunk Using Microsoft Excel 2007 but Hard to Make Good Graphs , 2022 .