Excel Chapter 10 of 24: ISERROR Function in Excel

Sometimes a formula will return an error message like #DIV/0, #NA and others. There is one way to avoid such results and it is by protecting you formulas with the ISERROR function. For example the formula =B1/A1 will return a #DIV/0 if A1 is empty or equal to zero. The formula will then look like this:
=IF(ISERROR(B1/A1),0,B1/A1)
In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of B1/A1.

The is a somewhat easy approach to do this specially if you are working with long formulas. First develop your formula without the ISERROR protection =B1/A1 then follow these steps.
insert between the equal sign and the original formula this piece of formula:
=IF(ISERROR(),0,9)B1/A1
Then copy the original formula at two places between the parentheses of the ISERROR condition and replacing the 9:
=IF(ISERROR(B1/A1 ),0,B1/A1 )B1/A1
Finally remove the original formula at the end of the new one.
=IF(ISERROR(B1/A1 ),0,B1/A1 )

Next Chapter: References in Excel

Excel Table of Contents
or use the quick links below

Discover Even More in 50 Excel spreadsheets

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

ISERROR Function in Excel