=A2/B2 shows #DIV/0! and the tooltip says Function DIVIDE parameter 2 cannot be zero. Often B2 isn't even a zero. It's empty. In arithmetic, Google Sheets treats a blank cell as 0.
Reproduce it in 30 seconds
| A (qty) | B (days) | C =A/B
|
|
|---|---|---|---|
| 2 | 10 | 0 | #DIV/0! |
| 3 | 10 | (blank) | #DIV/0! |
| 4 | 10 | 5 | 2 |
Row 4 is the control: same formula, nonzero divisor, no error. So the formula is fine. The divisor is the problem.
What it is not
- Not #N/A. Nothing is being looked up.
- Not #REF!. A2 and B2 still exist.
-
Not a formula parse error. The slash is valid syntax; parse errors come from things like
,vs;separators.
AVERAGE over a range with no numbers can also return #DIV/0!, because it divides a sum by a count of zero. Same error, same idea.
Fix 1: guard the divisor with IF (preferred)
A blank equals 0 in B2=0, so one test covers both broken rows:
=IF(B2=0,"",A2/B2)
Rows 2 and 3 become empty, row 4 still shows 2. Want a label instead? =IF(B2=0,"n/a",A2/B2).
Fix 2: IFERROR (only if any error should look like "no result")
=IFERROR(A2/B2,"")
This also hides #REF!, #N/A and #NAME? if the formula breaks in a different way later. That's why the IF version is safer when you care which error you're catching.
Fix 3: fix the input
If B2 should never be zero, put the real value in B2 and drop the wrapper. Wrappers are for rows where zero or blank is expected (a new item with no history, a template row). And if B2 is itself a formula that returned 0 when it should have returned 5, fix that formula; IF won't.
Originally published on SheetError, a free library of Google Sheets error fixes (I run it).
This article was created with the help of AI.
Top comments (0)