Replace Excel errors using this function
Q. I have an Excel spreadsheet that contains errors because some of the numbers that are part of the calculations are blank or zero. Is there a way to have Excel automatically return something instead of an error?
A. IFERROR can be used to eliminate formula errors and replace those errors with something else. The idea is that your formula would work as it normally does when there are no errors to return. However, if the formula results in an error, IFERROR “takes over” and produces an alternative result.
The alternatives could be text such as “Invalid,” “Not Found,” “Other,” or a number such as 0. You could also instruct Excel to leave that cell blank or perform some sort of calculation. IFERROR will provide a substitution for the following errors: #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, and #VALUE!.
Now let’s look at a specific example, shown in the screenshot below. You can view a video describing this process at the bottom of the page. You also can follow along by downloading this Excel workbook that contains the formulas without IFERROR on the first tab and the formulas with IFERROR on the second tab.
The screenshot below shows a spreadsheet calculating the Cost Per Unit for each product. For one product, the Quantity was left blank, and for another product, the Quantity is zero. Because the formula for Cost Per Unit is Total Cost ÷ Quantity, the Cost Per Unit for those two products just mentioned returns a divide by zero error (#DIV/0!).
To include IFERROR with this formula in order to eliminate these errors, put your cursor in the cell where the Cost Per Unit for the first product should appear. Click the Insert Function button (to the left of the Formula Bar) to open the Insert Function dialog box, shown below.
While the Search for a function: area is highlighted, type IFERROR and click OK. Select IFERROR from the Select a function: area.
The resulting Function Arguments dialog box, shown in the screenshot below, will appear. For Value, enter the formula for Cost Per Unit (B2/C2). For Value_if_error, type in the alternative result to display when the formula returns an error. In the example below, “” is entered to instruct Excel to leave the cell blank when an error is returned based on the formula. Click OK. Drag the formula down for all cells or double-click the bottom right corner of the first cell to fill them all in with the new IFERROR.
The result is that each of the products with a valid number for Total Cost and Quantity calculate a Cost Per Unit. The products with a Quantity of zero or a blank Quantity result in a blank cell for Cost Per Unit.
— By Kelly L. Williams, CPA, Ph.D.
About the authors
Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University. Byron Patrick, CPA/CITP, CGMA, is senior applications consultant at botkeeper.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
