Calculation errors in Excel
Posted by John on October 23, 2001 11:22 PM
I have heard through the grapevine that certain Excel functions produce errors. Functions such as NPV and IRR where mentioned. In addition, the entire linear regression package was mentioned.
It seems I cannot find any information to verify or dismiss these claims. Does anyone have any information on this?
Posted by Loren on October 24, 2001 8:45 AM
errors: swiped from Spreadsheet Style site
Accuracy
Doubts have been expressed in some quarters regarding the accuracy of Excels statistical functions. Undoubtedly there
were many errors in Excel 4, but most of these have been eliminated in the Excel 5 version used in this article. A former
student of mine, Andrew Harrison, conducted an extensive test of the probability functions in Excel 5 as part of an
undergraduate project. He compared tabulated values of the standard distributions from Excel and Minitab and concluded
that for all practical purposes the results were no different. Problems with Excels functions do occur in the extreme
margins of some of the tables. For example TINV(0.0000001, n) returns the arbitrary result 5,000,000 for any number of
degrees of freedom, whereas Minitab (wisely) refuses to calculate a percentage point for such a small probability. Some
disagreement was also found between Excel and Minitab in the percentage points of the F-distribution with n2=1 and tail
probability 0.001, but as the results are of the order of 500,000 this is perhaps to be expected!
