Mathematical Errors That Start Legends

Businesses depend on the clarity and usefulness of spreadsheets and their accurate data. One flawed entry can be an incredibly costly typo resulting in tens of thousands or even millions of dollars in losses. Flawed records can launch “official investigations,” divulge company secrets, or even create a situation where it appears a company or organization is far in debt when none exists. To get an idea of how pervasive the problem is, refer to the study by Coopers and Lybrand in England, in which they found that 90% of all spreadsheets with more than 150 rows that they audited contained errors. The reason for such discrepancies is often the spreadsheet’s reliance on previous cell data. One miscalculation entered into cell “7-c” can throw off every figure from that point on. This will render any mathematical data from cell “8-c” on completely erroneous. If your data compilation requires hundreds or thousands of rows, the potential for serious damage is only a keystroke away.

Spreadsheets have an illusionary quality that often creates a false sense of security. The row distribution can be flawless, the alignment perfect, and the compilation can appear absolutely masterful. Yet, it is within those seemingly flawless entries where the largest mistakes occur.

The European Spreadsheet Risks Interest Group (EuSpRIG) has a list of stories(1) where simple miscalculations and erroneous spreadsheet information has nearly obliterated many entities. Their reports show news from around the world where minor mistakes have lead to major problems.

Ivan Peterson’s “‘Mathtrek” (2) cited Greenville J. Croll’s ominous warnings of common spreadsheet flaws: “It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution. If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before [some random] event occurs, causing catastrophic loss.”

These mistakes aren’t limited to numeric figures. The “auto-complete” and “auto-correct” functions have also wreaked havoc in the spreadsheet world. Professors have found letter grades changed without their knowledge by auto-functions. Schools have nearly lost tens of thousands of dollars when numeric amounts were entered in alphabetic characters and ignored. Human error and the use of “auto” functions have created the majority of spreadsheet mistakes as seen by the world.

There are several ways to reduce the prevalence of errors in your spreadsheets. First and foremost, pay attention to your entries. If you are using a new macro or have developed a new function, test it completely before use. Another useful practice is to enter all information uniformly. This can be a difficult task if there are several individuals compiling the data; however it is imperative that everyone enter data precisely alike. Instructions can be documented and distributed within the group (i. e. no numbers written out in words, all data is entered wholly or abbreviated by everyone in the same manner, etc.). The spreadsheet project should also be initiated as soon as possible to allow the greatest time frame for both production and thorough review. You should consider disabling any “auto-correct” or other capabilities you may have enabled. Liberal use of control totals and cell protections will add an additional level of security and ‘peace of mind’ from unintentional changes. Once a spreadsheet has been tested and verified, use whatever tools available to maintain that ‘assured’ state.

Companies should fully anticipate a period of review for their datasheets to check for flaws. The process will seem time-consuming; however the end result will be successfully without error. There are companies and software that specialize in detecting and correcting spreadsheet errors. Proper documentation, standardization and automation are essential in minimizing risks.

Links:
(1) European Spreadsheet Risks Interest Group ( http://www.eusprig.org/stories.htm )
(2) Ivan Peterson’s “Mathtrek” ( http://www.maa.org/mathland/mathtrek_12_19_05.html )