Microsoft Office Tutorials and References

In Depth Information

**Understanding error values**

Understanding error values

An
error value
is the result of a formula that Excel can’t resolve. Table 12-2 describes the

seven error values.

TABLE 12-2
Error values

Error value

Cause

#DIV/0!

You attempted to divide a number by zero. This error usually occurs when

you create a formula with a divisor that refers to a blank cell.

#NAME?

You typed a name that doesn’t exist in a formula. You might have

mistyped the name or typed a deleted name. Excel also displays this error

value if you do not enclose a text string in quotation marks.

#VALUE

You entered a mathematical formula that refers to a text entry.

#REF!

You deleted a range of cells whose references are included in a formula.

#N/A

No information is available for the calculation you want to perform. When

building a model, you can type
#N/A
in a cell to show you are awaiting

data. Any formulas that reference cells containing the #N/A value return

#N/A.

#NUM!

You provided an invalid argument to a worksheet function. #NUM! can

indicate also that the result of a formula is too large or too small to be

represented in the worksheet.

#NULL!

You included a space between two ranges in a formula to indicate an

intersection, but the ranges have no common cells.

Using functions: A preview

In simplest terms, a
function
is a predeined formula. Many Excel functions are shorthand

versions of frequently used formulas. For example, compare =A1+A2+A3+A4+A5+A6+A7

+A8+A9+A10 with =SUM(A1:A10). The SUM function makes the formula shorter, easier to

read, and easier to create. Most Excel functions perform much more complex calculations,

like the PMT function, which lets you calculate a loan payment at a given interest rate and

principal amount.

All functions consist of a function name followed by a set of
arguments
enclosed in

parentheses. (In the preceding example, A1:A10 is the argument in the SUM function.) If you omit

a closing parenthesis when you enter a function, Excel adds the parenthesis after you press

Enter—as long as it’s obvious where the parenthesis is supposed to go. (Relying on this

feature can produce unpredictable results; for accuracy, always verify your parentheses.)

For more information about functions, see Chapter 13, “Using functions.” For more about the

SUM function, see Chapter 14.