Today’s topic is the Excel function IFERROR.
We’re building off of last week’s IF post and looking at a similar function this week. IFERROR is new to Excel as of 2007, so I’d like to start by offering a word of warning when using this function. If there is a chance that you or someone else will have to use your spreadsheet in an older version of Excel, don’t use this function.
That being said, this function is pretty useful and greatly improves the old way of nesting an ISERROR function within an IF function.
Here are the required inputs for the IFERROR function:
It is pretty straightforward. The first input, value, is a formula that you would like calculated and then displayed. The second input, value_if_error, is what Excel will display in the event that the first input results in an error. As always, an example is the best way to learn it:
- Download the example spreadsheet here. Yes it is the same file from last week.
- In column F we’d like to calculate the revenue generated per call to the customer. Go to cell F3 and type in “=C3/D3” then copy the formula to the rest of the rows.
- Notice in rows 6, 12, and 17 there are #DIV/0! errors. This is because those customers found us and didn’t require any sales calls. We would like to show their full sales amount in this metric. This is where IFERROR will come into play.
- Delete the formulas in column F. Now write a new formula into F3: “=IFERROR(C3/D3,C3)”
- Copy the formula to all the other rows and notice how there are no longer error codes. Great job.
The formula we wrote is saying, “divide the sales amount by the sales calls, but if that gives an error just return the sales amount.”
Next week we’ll wrap up the “logical series” by looking at two more functions: AND & OR. Get excited!
Loading ...