Exceljet

Quick, clean, and to the point

How to trap errors in formulas

In this video, we'll look at a few ways to trap errors. Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use.

In this first worksheet, we have a simple table that shows test scores for 5 sections. The total questions in each section is in column C, and the total questions that were correct is in column D.

Column D contains a formula that calculates the percentage score in each section by dividing the correct answers by the total number of questions in the section.

Notice we get a divide by zero error in column E for the last 2 sections. That's because there is no data yet to work with in those sections.

If you want to trap and suppress this error, there are a few easy options.

Let's go over three options in Columns G H and I.

First, you could test for a blank value in column C using the IF function with with the ISBLANK function.

=IF(ISBLANK(C6),"",D6/C6)

For the logical test use ISBLANK and C6 for the reference. For value if true, use empty double quotes, and for value if false use the original formula, D6/C6.

When I copy this formula down, we get the correct result for the first 3 sections and nothing is displayed for the next two sections.

This is a flexible way to trap errors since you are checking the source cells for any condition that you like. You can easily test for empty values, positive values, or values that match any other condition that makes sense.

Another way to trap this error is to use the IFERROR function which was introduced with Excel 2007.

IFERROR takes two arguments, value and value if error. For value, we supply the orignal formula, and for value if error, we use empty double quotes.

IF value is an error -- that is if our formula returns an error -- IFERROR will catch it and return the empty string. If there's no error, IFERROR will simply display the value...in this case, the result of the formula.

If you're using an older version of Excel you can also test for an error  using the IF function and the ISERROR function, which will return TRUE with any error.

In this case, the logical test is the ISERROR with the original formula. THe value if true is empty double quotes, and the value if false is the original formula.

=IF(ISERROR(D6/C6),"",D6/C6)

This works fine, but the structure is redundant and more error prone compared to IFERROR, so you'll want to use IFERROR if possible.

Finally, although we're using empty double quotes to display nothing when we detect an error condition, you could also display any message that you like. For example, you could display "not enough data" instead of an empty cell.

Course 
Core Formula
Author 
Dave Bruns