Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Figure 21-2: #DIV/0! errors occur when the data in column C is missing.
This formula calculates the percent change between the values in columns B and C. Data is not
available for months beyond May, so the formula returns a #DIV/0! error.
To avoid the error display, you can use an IF function to check for a blank cell in column C:
=IF(C2=0,””,(C2–B2)/C2)
This formula displays an empty string if cell C2 is blank or contains 0; otherwise, it displays the
calculated value.
Another approach is to use the IFERROR function to check for any error condition. The following
formula, for example, displays an empty string if the formula results in any type of error:
=IFERROR((C2–B2)/C2,””)
IFERROR was introduced in Excel 2007. For compatibility with previous versions, use this formula:
=IF(ISERROR((C2–B2)/C2),””,(C2–B2)/C2)
#N/A errors
The #N/A error occurs if any cell referenced by a formula displays #N/A.
Some users like to enter =NA() or #N/A explicitly for missing data (that is, Not
Available). This method makes it perfectly clear that the data is not available and hasn’t
been deleted accidentally.
The #N/A error also occurs when a lookup function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP)
can’t find a match.
 
Search JabSto ::




Custom Search