The IFERROR Function
Despite its name, the IFERROR function is actually pretty simple in construction. This function is composed of two parts – the original function that you wanted to create (such as dividing two numbers) and a value to use when this function doesn’t make sense (like trying to divide by 0 or a text term like “bananas"). Probably the best way to illustrate this function is to use an example.
Let’s take a basic table of data that contains three columns. The total amount of a restaurant bill is in the first column, and the second column is comprised of the number of people in the party that received the bill. In the third column, we want to calculate the price per person. (Click the image below for a larger view.)
In normal circumstances, we could just construct a column that would divide the first column (column A in this example) by the second (column B). In other words, since our first row of data is actually in the second row of the worksheet, the formula for the first cell in the Price per Person column would be:
Then, in a perfect world where all data entry is flawless, we could simply copy this formula and paste it into the remaining cells of that column. However, since our data does have some problems, we get the results shown in the following screenshot.
Because the sixth row of the table contains text instead of a number we get the #VALUE error when trying to calculate our formula. Likewise, we get a #DIV/0 error in the last row of data, because the formula is trying to divide by 0.
Now, let’s see what happens if we modify the original formula and use the IFERROR function instead. Our function would look like this:
This function will return the value of the original function if it can be calculated. Otherwise, it will return the text value “Not Defined".
You can modify the second argument of the IFERROR function to be anything you wish. For instance, if you would prefer the cell to just be blank when the function cannot be evaluated, you can modify it to:
This would yield the results in the screenshot below.
Tip: You need to include the quotation marks in the second argument of the IFERROR function if you want a text or “blank" value to be displayed. If you decide to use a numerical value instead, you can omit the quotation marks. For instance, if you want to display 0 whenever the function cannot be calculated, you could use the formula:
This result of this change in formula is shown below.