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.)