Avoid #DIV/0! with IFERROR
PremiumThe lamp has not sold yet, so B2/C2 gives #DIV/0!. In D2, wrap B2/C2 in IFERROR to show 0 instead.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Revenue | Units sold | Price per unit | ||
| 2 | Desk lamp | 0 | 0 | |||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock IFERROR and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:FILTERSORT and UNIQUE
It comes up in this mock test: Excel assessment (Indeed format)
Need help?
Why this works
Dividing by 0, or by an empty cell, gives #DIV/0!, and every SUM or AVERAGE that uses the cell turns into #DIV/0! too. IFERROR(B2/C2,0) returns the division when it works and 0 when it fails, so the column stays usable.
Common mistake: Writing "0" in quotes. That is text, and later sums skip it or give #VALUE!.
The answer
- D2
=IFERROR(1B2/C2,20)
In plain words: 1Divide the revenue in B2 by the units sold in C2, and 2if that gives an error, show 0 instead.
Wrap the division in IFERROR, for example =IFERROR(B2/C2,0), or test the divisor first: =IF(C2=0,0,B2/C2).