IFERROR

Excel functions                              Microsoft Excel Examples

 

 

Excel tips on functions and formulas

Excel Functions and Formulas

Excel examples

Excel and VBA Expert

Excel fonctions et formules

Special

Excel Functions

2003-2010

 

sumproduct

index-match

subtotal

iserror

countif

sumif

hlookup

lookup

vlookup

 

 

Special

Excel Functions

2007-2010

 

averageif

averageifs

countifs

sumifs

iferror

 

 

 

 

 

 

 

Excel Functions and Formulas

 

Excel Macros and VBA

Lesson 25: IFERROR  Function (Excel 2007)

 

A

B

C

D

1

Month

Unit Price

Total
Amount

Quantity

2

May

 

365.89

#DIV0

3

Jun

9.95

5698.21

572.6844

4

Mar

32.46

6523.45

200.9689

5

Dec

NH

7568.66

#VALUE

6

Nov

0

569.41

#DIV/0

7

Oct

458.65

4569.52

9.962978

The IFERROR function will protect you from error messages like the ones above #DIV0 or #VALUE and any other error message generated by a faulty formula. Above the formula in cell D2 is =C2/B2 and the formula has been copied down to D7. The error message #DIV/0 in cell D2 is due to the fact that cell B2 is empty, the error message #VALUE in cell D5 is due to the presence of a non numerical value in cell B5 and the error message in D6 is due to the zero value in cell D5.

The error messages can be replaced using the IFERROR function. For example:

The formula in D2 is =C2/B2 and if the formula was =IFERROR(C2/B2,0) the result would be 0 instead of the error message. If the formula was =IFERROR(C2/B2,"") the result would be an empty cell. If the formula was =IFERROR(C2/B2,99) the result would be 99 and if the formula was =IFERROR(C2/B2,"Review") the result would be "Review".

The general format is: =IFERROR(if what formula is wrong, what result).

Important Note: The IFERROR function was created for Excel 2007. If you share your workbooks with colleagues who use earlier versions of Excel the function IFSERROR will not work, it will just generate an error message #NAME. So use the formula IF/ISERROR instead as it works in all versions of Excel.


Excel Functions and Formulas Sitemap


Go to the next lesson:
Lesson 26: 5 Useful Tips on Excel Functions and Formulas

 

 

 

Excel tips on functions and formulas

Excel Functions and Formulas

Excel examples

Excel and VBA Expert

Excel fonctions et formules

SUMPRODUCT    INDEX/MATCH    SUBTOTAL    ISERROR    COUNTIF    SUMIF    HLOOKUP    LOOKUP    VLOOKUP
AVERAGEIF    AVERAGEIFS    COUNTIFS    SUMIFS    IFERROR