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.
It comes up in this mock test: Excel assessment (Indeed format)
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.