Spreadsheet errors are prevalent in business, and their costs are tremendous. The sidebar lists several investigations into these problem and their costs.
Sources of Spreadsheet Errors
Errors are caused by many factors, including poor development practices, formulas improperly copied from one cell to another, and errors in specific formula text.
Generally speaking, these errors can be described as practice errors, structural errors, and computational errors.
Practice errors result from failing to implement and/or adequately follow best practices such as how to incorporate constants in formulas. Best practice dictates putting constants in cells where they can be readily identified and referenced. Then, if a constant must be changed, only one cell has to be edited, not the many cells that contain the constant as a literal. Organizations that want to reduce or eliminate practice errors often utilize the services of training companies (see sidebar) that provide best practices education.
Structural errors involve relationships among cells in a spreadsheet. One common error involves formulas replicated by copying them across a range of rows or columns. The copied formulas are structurally the same, differing only in the relative references they contain. An error can occur, however, if one cell in the range is changed so as to break the relationship.
Many structural errors can be found by spreadsheet auditing software (see the sidebar). Such software can take the form of a plugin or standalone program that can read a spreadsheet file. In either case, the auditing software can produce a report of structural inconsistencies as well as specific practice errors.
Computational errors arise from several common kinds of mistakes in formulas.
A complex formula often is composed of subexpressions, which in turn may also be composed of subexpressions. If the overall formula evaluates incorrectly, the problem may be due to an incorrect subexpression. Finding that subexpression can be very difficult, particularly when it occurs in an unused branch of a conditional function.
Evaluation of a complex formula depends on the order of operations. Parentheses control the order, but the arrangement and grouping of these slim characters can be difficult to audit. Problems arise when the text of a formula contains multiple parentheses making it difficult to ascertain how a formula is evaluated by simple visual examination. This problem is exacerbated during the editing process. At times, Excel may refuse to save an edited formula because parentheses are not balanced. At other times, the parentheses may be inserted incorrectly. Although Excel often offers a suggestion to fix unbalanced parentheses, it can be difficult to see if the suggestion is appropriate or what the right formula text should be.
Sometimes a formula contains the same subexpression multiple times. If the subexpression isn’t copied exactly, it can introduce errors. Subtle changes in how characters are arranged can be difficult to see, and repetitive expressions in Excel’s character-based Formula Bar make a formula difficult to read.
Complex Excel formulas often contain branching functions, such as IF or CHOOSE, to create branches for conditional evaluation. Creating, testing, and editing these formulas can be difficult, particularly when branching functions are embedded in the arguments of other branching functions.
Stage One Software’s Products: FormulaForge and FormulaView
To date, there has been only limited help for finding and fixing computational errors, mainly in Excel’s built-in function tool and in its help files. Stage One Software remedies this defect by offering the first tools specifically addressed at eliminating computational errors.
The Text View shows the formula’s text as it would appear in Excel’s formula bar.
The Math View shows the formula as it would be typeset in a book on mathematics.
The Tree View shows the formula’s structure using a hierarchical diagram.
Both products synchronize these multiple views as users explore and/or modify a formula.
FormulaForge allows users to create and modify formulas using a rich collection of software tools including drag and drop editing.
FormulaView provides the same synchronized views as does FormulaForge but does not provide facilities for creating or modifying formulas.
FormulaView is available as a free download from this site. FormulaForge is being beta tested and is available for evaluation by interested parties.