You have probably all seen Excel formulas that result in an error such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, or #VALUE!. Usually they indicate that you need to correct something in your formula or the data it references. But sometimes the condition that produces the error is unavoidable. In those cases not only does the error look bad, it can also interfere with formulas that reference that cell.
Here’s an example of a situation in which the conditions causing the error are unavoidable. In this spreadsheet we’re calculating the percentage change in sales from year 1 to year 2. So we have to divide the change amount by year 1 sales. For Item 2, which had no sales in year 1, that creates a divide by zero error.
|
A |
B |
C |
D |
E |
Column E Formula | |
|
1 |
Year 1 Sales |
Year 2 Sales |
$ Change |
% Change |
||
|
2 |
Item 1 | 10,000 | 11,000 | 1,000 | 10% | =D2/B2 |
|
3 |
Item 2 | 0 | 7,000 | 7,000 | #DIV/0! | =D3/B3 |
|
4 |
Item 3 | 15,000 | 12,000 | (3,000) | -20% | =D4/B4 |
One way to deal with that is by embedding your formula within the IFERROR() function. IFERROR is very simple. It’s format is just IFERROR(value, value if error). So in the above example, instead of entering =D2/B2 in cell E2, you would enter =IFERROR(D2/B2,”-“). When you copy that formula down, then % Change for Item 2 would show a dash instead of the divide by 0 error.
I chose to display a dash when my formula resulted in an error, but you could use anything you want whether it’s text,a number,or a formula. Just remember that if you to use text, you must enclose it in quotation marks.
IFERROR isn’t just about making your spreadsheet look better. It can also prevent errors in other formulas. If a formula in another cell references the cell containing an error, either directly or within a range (such as when you sum a column) the first error will cause an error in the second formula too. For example, if you tried to sum column E in the above example, your sum would display #DIV/0! instead of -10%. But avoiding the original error by using IFERROR also prevent errors in any dependent formulas.
By the way, for those of you wondering about my math skills, I understand that you would not want to sum the % changes. But that was faster than writing a second example to demonstrate my point.
