We all believe that computers don't make mistakes, don't we? Well, they're great at binary but as soon as we ask them to do more complex tasks, then we can start to introduce errors and problems. Here are some problems which can arise in Excel leading to incorrect answers.
Why Excel Makes Mistakes
Excel can do sums in binary almost perfectly and very quickly, too. Unfortunately, we expect it to do much more, handle different data types and apply complex processing algorithms. Sometimes, the results can be unexpected, even if they are not a formal error. In this article, we will consider three types of problems which can occur in Excel. In the application's defense, some of these are misunderstandings of the way it works rather than actual errors.
Actual Bugs Reported With Excel 2007
The web forums consistently report a problem with Excel 2007 in dealing with specific combinations. The bug reported on web analytics India in January 2009 and the Microsoft public Google groups as early as September 2007 appears to arise from the multiplication of two numbers that should produce 65535, such as 850 and 77.1 and actually produces 100000! Tests with Excel 2003 show that this edition did not suffer from this problem. Bugs like this get fixed in service packs or hotfixes. This error was fixed by Microsoft in a hot fix and in Office 2007 Service Pack 1.
Data Entry Anomalies
The following two columns of dates look very similar to us humans:
- 21st June 2003, 21 June 2003
- 17th May 2004, 17 May 2004
- 1st August 2005, 01 August 2005
- 2nd Feb 2005, 02 February 2005
- 31st March 2007, 31 March 2007
- 5th May 2008, 05 May 2008
- 16th June 2008, 16 June 2008
- 28th August 2008, 28 August 2008
However if we sort on the left hand column in Excel, the results look bizarre:
- 16th June 2008, 16 June 2008
- 17th May 2004, 17 May 2004
- 1st August 2005, 01 August 2005
- 21st June 2003, 21 June 2003
- 28th August 2008, 28 August 2008
- 2nd Feb 2005, 02 February 2005
- 31st March 2007, 31 March 2007
- 5th May 2008, 05 May 2008
Whereas if we now re-sort on the right hand column, we get more sensible results:
- 21st June 2003, 21 June 2003
- 17th May 2004, 17 May 2004
- 1st August 2005, 01 August 2005
- 2nd Feb 2005, 02 February 2005
- 31st March 2007, 31 March 2007
- 5th May 2008, 05 May 2008
- 16th June 2008, 16 June 2008
- 28th August 2008, 28 August 2008
This is because as far as Excel is concerned, the left hand column is text, and the right hand column is a set of cells in date format. Obvious when you know but disconcerting if you don't!
Calculation Errors
Some authors have carried out extensive tests to test the more complex functions in Excel. For example, Iain Buchan a leading UK statistician argued that there were discrepancies in the statistical analysis toolpak add-on supplied with Excel and developed his own add-on known as Arcus QuickStat to provide reliable and traceable statistical results from Excel to address issues raised by his own work and that of others. He also reports serious discrepancies in the core Excel function CHIINV() arguing that the approximations used are too imprecise to be valid.
Conclusions
We assume that Excel will give us the correct answers. This is not always the case. Sometimes, we can introduce errors ourselves by misunderstanding how Excel handles data. At other times, errors are caused by bugs. These will be fixed so make sure you have the latest fixes and service packs. Finally, some of the algorithms used in the more complex functions appear to have limitations. If you are concerned about this, you may need to switch to an application such as SPSS.
