Microsoft Office Tutorials and References

In Depth Information

**Understanding Error Messages in Formulas**

•
When you type a closing parenthesis, Excel shows the opening paren-

thesis in bold for a fraction of a second. This would be more helpful

if Excel kept the opening parenthesis in bold for 5 seconds or 20

seconds. However, when it is displayed for only about half a second,

it is nothing more than a frustrating reminder that your reflexes are

not fast enough.

Understanding Error Messages in Formulas

Don
’
t be frustrated when a formula returns an error result. This eventu-

ally happens to everyone. The key is to understand the difference between the

various error values so that you can begin to troubleshoot the problem.

As you enter formulas, you might encounter a number of errors, including

those listed next:

•
#VALUE!

#VALUE!
—
This error indicates that you are trying to do math with

nonnumeric data. For example, the formula =4+"apple" returns a

#VALUE! error. This error also occurs if you try to enter an array

formula, but fail to use Ctrl+Shift+Enter, as described in
Chapter 17
,

•
#DIV/0!

#DIV/0!
—
This error occurs when a number is divided by zero
—
that is,

when a fraction
’
s denominator evaluates to zero.

•
#REF!

#REF!
—
This error occurs when a cell reference is not valid. For

example, this error can occur if one of the cells referenced in the for-

mula has been deleted. It can also occur if you cut and paste another

cell over a cell referenced in this formula. You may also get this er-

ror if you are using Dynamic Data Exchange (DDE) formulas to link to

external systems and those systems are not running.

•
#N/A!

#N/A!
—
This error occurs when a value is not available to a func-

tion or a formula. #N/A! errors most often occur because of key val-

ues not being found during lookup functions. They can occur as a res-

ult of HLOOKUP, LOOKUP, MATCH, or VLOOKUP. They can also

result when an array formula has one argument that is not the same

shape as the other arguments or when a function omits one or more re-

quired arguments. Interestingly, when an #N/A! error enters a range,

all subsequent calculations that refer to the range have a value of

#N/A!.