Microsoft Office Tutorials and References

In Depth Information

**Finding and correcting errors in calculations**

Finding and correcting errors in calculations

Including calculations in a worksheet gives you valuable answers to questions about your

data. As is always true, however, it is possible for errors to creep into your formulas. With

Excel, you can find the source of errors in your formulas by identifying the cells used in a

particular calculation and describing any errors that have occurred. The process of

examining a worksheet for errors is referred to as
auditing
.

Excel identifies errors in several ways. The first way is to display an error code in the cell

holding the formula that is generating the error.

3

When a cell with an erroneous formula is the active cell, an Error button is displayed next

to it. Pointing to the Error button causes it to display an arrow on the button’s right edge.

Clicking the arrow displays a menu with options that provide information about the error

and offer to help you ix it.

The following table lists the most common error codes and what they mean.

Error code

Description

#####

The column isn’t wide enough to display the value.

#VALUE!

The formula has the wrong type of argument (such as text in a cell where a

numerical value is required).

#NAME?

The formula contains text that Excel doesn’t recognize (such as an unknown

named range).

#REF!

The formula refers to a cell that doesn’t exist (which can happen whenever

cells are deleted).

#DIV/0!

The formula attempts to divide by zero.

#N/A!

The formula attempts to use a value that is not available in the target range.

This error often occurs when a user enters an invalid lookup value in a

VLOOKUP formula.