Microsoft Office Tutorials and References

In Depth Information

**Solving Formula Errors**

Solving Formula

Errors

Note:
The SUBSTITUTE() function always performs a case-sensitive search. That means if you try using

SUBSTITUTE() to replace the word
it, in the previous example, Excel won’t match
It.

Solving Formula Errors

Errors… they happen in the most unexpected places, transforming rows of

calculations into unhelpful error codes like
#NAME?, #VALUE!
, and
#MORON!
(OK, that

last one doesn’t actually appear in Excel, but it might as well, given the sense of defeat

and frustration these error codes can give you.) In some cases, you can see how to

fix an error just by looking at the formula. However, sometimes the problem isn’t so

easy to solve, especially if your formulas perform calculations using the results of

other
formulas. In such cases, you can have a tough time tracking down where the

original error happened.

Excel provides some interesting
formula auditing tools
—a handful of features that

you can use to inspect broken formulas or figure out what’s going on in really

complex ones. These tools make it much easier to fix errors.

With any error, your first step is to identify the error code by using the information

listed in Table 17-2 on page 476. If the problem isn’t immediately obvious, then you

can use the Formula Auditing tools to perform the following tasks:

• Evaluate an expression step-by-step, until you hit the error. That way, you know

exactly what part of the formula’s causing the error.

• Trace the
precedents
of a formula that’s causing an error. Precedents are the cells

that a particular formula references. In the formula =
, both A1 and B1
B1

are precedents. If either one of these cells contains an error, then the error gets

fed into—and trips up—the formula.

• Trace the
dependents
of a cell. Dependents are other cells that use the current

cell. If one cell has the formula =
, and another cell contains =
A1*10
, both
B1

these cells are dependents of cell A1. If A1 has an error, it infects both formulas.

• Perform an error check on the entire worksheet. Excel’s error check is like a spell

check. One by one, it takes you to each cell that has an unresolved problem.

To perform any of these tasks, you’ll need the Formulas tab’s Formula Auditing

section. The following sections explain how you use it to find errors, evaluate formulas

piece-by-piece, and trace relationships.

Step-by-Step Evaluation

Complex formulas usually include multiple
sub-expressions
. Each sub-expression is a

piece of any formula that’s evaluated separately. It may be an arithmetic operation in

parentheses, a nested function, or even just a cell reference. In order to understand

what’s causing an error in your formula, you need to know which sub-expression

caused the problem.