Spreadsheet Errors: Part 1

I’ve stumbled upon a treasure trove of math errors. The European Spreadsheet Risks Interest Group (EUSRIG) maintains a collection of stories at www.eusprig.org/stories.htm. According to their site, the stories “illustrate common problems and errors that occur with the uncontrolled use of spreadsheets, with comments on the risk and possible avoidance action.”

The phrase “uncontrolled use of spreadsheets” seems to mean not having checks and balances in place (as opposed to what I envision, which is a person sitting at the computer with about 39 spreadsheets open). Each of the stories is cited and linked to the original source and then summarized. For example, you can read about:

• how numbers were added incorrectly in a school budget, resulting in a £30,000 shortfall, because some of the numbers were entered as text and therefore not included in the =Sum() command. (Story #67)
• how the AutoComplete function on Excel caused a professor to realize that the wrong grades were being entered, because Excel was being helpful and changing “A”s to “A-”s. (Story #51, but this is the exact reason why I have AutoComplete OFF in Excel)
• how researchers in bioinformatics found that Excel, still being helpful, changed some gene names (like SEPT2) to dates. (Story #39)

Look for some of the highlights from this site to be posted here in the weeks to come.

Sidenote: The picture in the upper left is one of my favorite Excel errors, though it’s actually a consequence of how data is stored rather than data being entered incorrectly. If you enter 0.8 in one cell and then fill down subtracting 0.1 from the cell above, the cell that should contain zero will contain 1.39 × 10^-16.

By starting with different numbers (like 1.6 or 2) you can get zero to equal all sorts of other values (although sometimes, as when you start with .7 or 1.2, it will just equal zero).

Tags: computation errors, Excel, spreadsheet