Part IV: Using Advanced Excel Features
654
FIGURE 32.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 isn’t
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 an 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,””)Note
The IFERROR function 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.Note
Some users like to use =NA() or #N/A explicitly for missing data. This method makes it perfectly clear that the
data is not available and hasn’t been deleted accidentally. n