Microsoft Office Tutorials and References

In Depth Information

**Detecting and Correcting Errors in Formulas**

Table 3-2

Common Formula Error Messages

Message

What Went Wrong

#DIV/0!

You tried to divide a number by a zero (0) or an empty cell.

#NAME

You used a cell range name in the formula, but the name isn’t

defined. Sometimes this error occurs because you type the name

incorrectly. (Earlier in this chapter, “Naming cell ranges so that

you can use them in formulas” explains how to name cell ranges.)

#N/A

The formula refers to an empty cell, so no data is available for

computing the formula. Sometimes people enter N/A in a cell as

a placeholder to signal the fact that data isn’t entered yet. Revise

the formula or enter a number or formula in the empty cells.

#NULL

The formula refers to a cell range that Excel can’t understand.

Make sure that the range is entered correctly.

An argument you use in your formula is invalid.

#NUM

#REF

The cell or range of cells that the formula refers to aren’t there.

#VALUE

The formula includes a function that was used incorrectly, takes

an invalid argument, or is misspelled. Make sure that the function

uses the right argument and is spelled correctly.

Book III

Chapter 3

To find out more about a formula error and perhaps correct it, select the cell

with the green triangle and click the Error button. This small button appears

beside a cell with a formula error after you click the cell. The drop-down list

on the Error button offers opportunities for correcting formula errors and

finding out more about them.

Running the error checker

Another way to tackle formula errors is to run the error checker. When the

checker encounters what it thinks is an error, the Error Checking dialog box

tells you what the error is, as shown in Figure 3-10. To run the error checker,

go to the Formulas tab and click the Error Checking button (you may have to

click the Formula Auditing button first, depending on the size of your screen).

If you see clearly what the error is, click the Edit in Formula Bar button, repair

the error in the Formula bar, and click the Resume button in the dialog box

(you find this button at the top of the dialog box). If the error isn’t one that

really needs correcting, either click the Ignore Error button or click the Next

button to send the error checker in search of the next error in your worksheet.