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 result.

•
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

problem.

•
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

correcting them.

Mismatched parentheses

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:

=SUM(A1:A500