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 ,
Using Super Formulas in Excel .
#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!.
Search JabSto ::




Custom Search