How to use IFERROR to show a blank instead of an error
A single divide by zero fills a column with #DIV/0!. IFERROR runs the sum you wanted and quietly puts something readable in its place when it fails.
The formula
=IFERROR(A2/B2,"") in column C
What each part does
A2/B2- The sum you actually want. Dividing by zero is what breaks it.
IFERROR- Runs that sum, and steps in only when it comes back as an error.
""- What to show instead. Two quotes with nothing between them means an empty cell.
How it works, and what to watch for
The general idea: IFERROR takes two things. The sum to try, and what to show if that sum comes back as an error.
It catches every kind of error, including ones caused by a genuine mistake in the formula. Get the formula right first, then wrap it, or you may hide a problem you needed to see.
For a lookup, IFNA is the better choice. It catches only #N/A, which is the one a lookup produces when it finds nothing, and leaves real mistakes visible.
Two quotes with nothing between them give an empty-looking cell. A zero or a short note such as "not yet" works just as well, and often reads better on a report.
Questions
What is the difference between IFERROR and IFNA?
IFERROR hides every error. IFNA hides only #N/A. For lookups, IFNA is safer, because it still shows you a broken formula.
Does IFERROR slow a sheet down?
It runs the inner formula twice in older versions of Excel, so on very large sheets it can. On a normal sheet the difference is not noticeable.
Is the cell really empty?
No. It holds a formula that returns empty text, so ISBLANK still says it is not blank. That matters if you count blanks later.
The example sheet
| Sold |
Days |
Per day |
| 120 |
4 |
|
| 80 |
0 |
|
| 150 |
5 |
|
| 90 |
0 |
|
If this helped, these are close by
More on IFERROR, and every walkthrough that uses it.
