Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

Research on spreadsheet errors

Using a spreadsheet can be hazardous to your company’s bottom line. It’s tempting to simply

assume that your spreadsheet produces accurate results. If you use the results of a spreadsheet

to make a major decision, it’s especially important to make sure that the formulas return

accurate and meaningful results.

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 interests you, I urge you to check out the Spreadsheet Research

(SSR) Web site maintained by Raymond Panko of the University of Hawaii. The URL is

http://panko.shidler.hawaii.edu/SSR/

Formula Problems and Solutions

Formula errors tend to fall into one of the following general categories:

h
Syntax errors:
You have a problem with the syntax of a formula. For example, a formula

may have mismatched parentheses, or a function may not have the correct number of

arguments.

h
Logical errors:
A formula does not return an error, but it contains a logical flaw that

causes it to return an incorrect result.

h
Incorrect reference errors:
The logic of the formula is correct, but the formula uses an

incorrect cell reference. As a simple example, the range reference in a SUM formula may

not include all the data that you want to sum.

h
Semantic errors:
An example of a semantic error is a function name that is spelled

incorrectly. Excel attempts to interpret the misspelled function as a name and displays the

#NAME? error.

h
Circular references:
A circular reference occurs when a formula refers to its own cell,

either directly or indirectly. Circular references are useful in a few cases, but most of the

time, a circular reference indicates a problem.

h
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 formula may return an error or (even worse) an

incorrect result.

h
Incomplete calculation errors:
The formulas simply aren’t calculated fully. Microsoft has

acknowledged problems with Excel’s calculation engine in some versions of Excel. To

ensure that your formulas are fully calculated, press Ctrl+Alt+F9.