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.
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.