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.