Microsoft Office Tutorials and References

In Depth Information

**It Takes All Types**

When one of your formulas returns one of these error values, an alert

indicator (in the form of an exclamation point in a diamond) appears to the left of

the cell when it contains the cell pointer, and the upper-left corner of the cell

contains a tiny green triangle. When you position the mouse pointer on this

alert indicator, Excel displays a brief description of the formula error and adds

a drop-down button to the immediate right of its box. When you click this

button, a pop-up menu appears with a number of related options. To access

online help on this formula error, including suggestions on how to get rid of

the error, click the Help on This Error item on this pop-up menu.

The worst thing about error values is that they can contaminate other

formulas in the worksheet. If a formula returns an error value to a cell and a second

formula in another cell refers to the value calculated by the first formula, the

second formula returns the same error value, and so on down the line.

After an error value shows up in a cell, you have to discover what caused

the error and edit the formula in the worksheet. In Table 2-1, I list some error

values that you might run into in a worksheet and then explain the most

common causes.

Table 2-1

Error Values That You May Encounter

from Faulty Formulas

What Shows

Up in the Cell

What’s Going On Here?

#DIV/0!

Appears when the formula calls for division by a cell that either

contains the value 0 or, as is more often the case, is empty.

Division by zero is a no-no in mathematics.

#NAME?

Appears when the formula refers to a
range name
(see Chapter

6 for info on naming ranges) that doesn’t exist in the worksheet.

This error value appears when you type the wrong range name

or fail to enclose in quotation marks some text used in the

formula, causing Excel to think that the text refers to a range name.

#NULL!

Appears most often when you insert a space (where you should

have used a comma) to separate cell references used as

arguments for functions.

#NUM!

Appears when Excel encounters a problem with a number in the

formula, such as the wrong type of argument in an Excel function

or a calculation that produces a number too large or too small to

be represented in the worksheet.

#REF!

Appears when Excel encounters an invalid cell reference, such

as when you delete a cell referred to in a formula or paste cells

over the cells referred to in a formula.

#VALUE!

Appears when you use the wrong type of argument or operator

in a function, or when you call for a mathematical operation that

refers to cells that contain text entries.