Microsoft Office Tutorials and References

In Depth Information

**Formula Errors**

Formula Errors

When you click the exclamation mark icon next to an error, you see a menu of

choices (as shown in Figure 17-7):

•
Help on this error
pops open Excel’s online help, with a (sometimes cryptic)

description of the problem and what could have caused it.

•
Show Calculation Steps
pops open the Evaluate Formula dialog box, where you

can work your way through a complex formula one step at a time.

•
Ignore Error
tells Excel to stop bothering you about this problem, in any

worksheet you create. You won’t see the green triangle for this error again (although

you’ll still see the error code in the cell).

•
Edit in Formula Bar
brings you to the formula bar, where you can change the

formula to fix a mistake.

•
Error Checking Options
opens up the Excel Options dialog box, and brings

you to the section where you can configure the settings Excel uses for

alerting you about errors. You can turn off
background error checking
, or change

the color of the tiny error triangles using the settings under the Error

Checking heading. (Background error checking is the feature that flags cells with tiny

green triangles when the cells contain a problem.) You can also tell Excel to start

paying attention to errors you previously told it ignore by clicking the Reset

Ignored Errors button. Underneath that button is a section named “Error

checking rules” that has a number of options that focus on specific types of errors. For

example, you can choose to have Excel ignore numbers stored as text, formulas

that ignore part of a range, and other situations that technically aren’t errors, but

usually indicate that you’ve done something you didn’t mean to. Excel
always

reports genuine errors, like #VALUE! and #NAME? regardless of what choices

you make in this dialog box.

Note:
Sometimes a problem isn’t an error, but simply the result of data that hasn’t yet been entered.

In this case, you can solve the problem by using a conditional
error-trapping formula
. This conditional

formula checks if the data’s present, and it performs the calculation only if it is. The next section shows

one way to use an error-trapping formula.

Table 17-2 lists the error codes that Excel uses.

Table 17-2.
Excel’s error codes

Error Code

Description

#VALUE!

You used the wrong type of data. Maybe your function expects a single value

and you submitted a whole range. Or, more commonly, you might have used

a function or created a simple arithmetic formula with a cell that contains text

instead of numbers.

#NAME?

Excel can’t find the name of the function you used. This error code usually

means you misspelled a function’s name, although it can indicate you used text

without quotation marks or left out the empty parentheses after the function

name. This error will also happen if you try to use a new Excel 2010 function in

older version of Excel.