Microsoft Office Tutorials and References
In Depth Information
Researchers have conducted quite a few studies that deal with spreadsheet errors. Generally, these studies have
found that between 20 and 40 percent of all spreadsheets contain some type of error. If this type of research in-
terests you, I urge you to check out the Spreadsheet Research (SSR) website maintained by Raymond Panko of
the University of Hawaii. The URL is
Formula Problems and Solutions
Formula errors tend to fall into one of the following general categories:
• Syntax errors: You have a problem with the syntax of a formula. For example, a formula may have mis-
matched parentheses, or a function may not have the correct number of arguments.
• Logical errors: A formula does not return an error, but it contains a logical flaw that causes it to return an
• Incorrect reference errors: The logic of the formula is correct, but the formula uses an incorrect cell refer-
ence. As a simple example, the range reference in a SUM formula may not include all the data that you
want to sum.
• Semantic errors: An example of a semantic error is a function name that is spelled incorrectly. Excel at-
tempts to interpret the misspelled function as a name and displays the #NAME? error.
• Circular references: A circular reference occurs when a formula refers to its own cell, either directly or in-
directly. Circular references are useful in a few cases, but most of the time, a circular reference indicates a
• Array formula entry error: When entering (or editing) an array formula, you must press Ctrl+Shift+Enter
to enter the formula. If you fail to do so, Excel does not recognize the formula as an array formula. The for-
mula may return an error or (even worse) an incorrect result.
• Incomplete calculation errors: The formulas simply aren't calculated fully. To ensure that your formulas
are fully calculated, press Ctrl+Alt+Shift+F9.
Syntax errors are usually the easiest to identify and correct. In most cases, you will know when your formula
contains a syntax error. For example, Excel won't permit you to enter a formula with mismatched parentheses.
Other syntax errors also usually result in an error display in the cell.
The remainder of this section describes some common formula problems and offers advice on identifying and
In a formula, every left parenthesis must have a corresponding right parenthesis. If your formula has mis-
matched parentheses, Excel usually won't permit you to enter it. An exception to this rule involves a simple for-
mula that uses a function. For example, if you enter the following formula (which is missing a closing paren-
thesis), Excel accepts the formula and provides the missing parenthesis: