Microsoft Office Tutorials and References

In Depth Information

**Formula Errors**

Naming objects

In addition to providing names for cells and ranges, you can give more meaningful names to

objects such as pivot tables and shapes. Using meaningful names can make referring to such

objects easier, especially when you refer to them in your VBA code.

To change the name of a nonrange object, use the Name box, which is located to the left of the

formula bar. Just select the object, type the new name in the Name box, and then press Enter.

If you simply click elsewhere in your workbook after typing the name in the Name box,

the name won’t stick. You
must
press Enter.

For some reason, Excel doesn’t allow you to use the Name box to rename a chart. You must use

Chart Tools➜Layout➜Properties➜Chart Name.

Formula Errors

Entering a formula and receiving an error in return isn’t uncommon. One possibility is that the

formula you entered is the cause of the error. Another possibility is that the formula refers to a

cell that has an error value. The latter scenario is known as the
ripple effect
— a single error value

can make its way to lots of other cells that contain formulas that depend on the cell. The tools in

the Formulas➜Formula Auditing group can help you trace the source of formula errors.

Table 3-2 lists the types of error values that may appear in a cell that has a formula.

Table 3-2:
Excel Error Values

Error Value

Explanation

#DIV/0!

The formula is trying to divide by 0 (zero), an operation that’s not allowed on this planet.

This error also occurs when the formula attempts to divide by a cell that is empty.

#N/A

The formula is referring (directly or indirectly) to a cell that uses the NA worksheet

function to signal the fact that data isn’t available. A LOOKUP function that can’t locate a value

also returns #N/A.

#NAME?

The formula uses a name that Excel doesn’t recognize. This can happen if you delete a

name that’s used in the formula or if you have unmatched quotes when using text. A

formula will also display this error if it uses a function defined in an add-in and that add-in

isn’t installed.

#NULL!

The formula uses an intersection of two ranges that don’t intersect. (This concept is

described in the section “Intersecting names,” earlier in the chapter.

#NUM!

There is a problem with a function argument; for example, the SQRT function is attempting

to calculate the square root of a negative number. This error also appears if a calculated

value is too large or too small. Excel doesn’t support nonzero values less than 1E–307 or

greater than 1E+308 in absolute value.

continued