Formula Errors

It's not uncommon to enter a formula and receive an error in return. Formulas can return an error value if a cell that they refer to has an error value. This 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 Excel Formula Auditing toolbar contains tools that 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

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.

The formula is referring (directly or indirectly) to a cell that uses the NA worksheet function to signal the fact that data is not available. A LOOKUP function that can't locate a value also returns #NA.

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 is not installed.

The formula uses an intersection of two ranges that don't intersect. (This concept is described earlier in the chapter.)

Continued

Table 3-2 EXCEL ERROR VALUES (Continued)

Error Value

#VALUE!

Explanation

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 small. Excel does not support non-zero values which are less than 1E-307 or greater than 1E+308 in absolute value.

The formula refers to a cell that isn't valid. This can happen if the cell has been deleted from the worksheet.

The formula includes an argument or operand of the wrong type. An operand is a value or cell reference that a formula uses to calculate a result. This error also occurs if your formula uses a custom VBA worksheet function that contains an error.

0 0

Post a comment