The #DIV/0! error
A division by zero, or by a cell that is still empty: it is the error of tables prepared before the figures arrive. One condition prevents it.
What it means
#DIV/0! means a division has zero as its divisor. An empty cell counts as zero.
Where it comes from
- An empty divisor: the growth rate
=(C2-B2)/B2while B2 is not filled in yet. - A zero divisor: a zero quantity, a zero total.
- AVERAGE with no number at all: every cell is empty or holds text (numbers stored as text, often).
- AVERAGEIF or AVERAGEIFS with no row matching the criteria.
Fixing it
- Test the divisor:
=IF(B2=0,"",(C2-B2)/B2). - Check that the numbers really are numbers: numbers stored as text do not count in AVERAGE. Convert text to numbers fixes them.
- For AVERAGEIF: check the criterion (spaces, text case, dates stored as text).
Hiding it, when it is expected
=IFERROR((C2-B2)/B2,"") is acceptable here: the only possible error of a simple division is #DIV/0!. On a more complex formula, prefer testing the divisor, which hides nothing else.
Tools that help
- Find the right formula: percentage change and share of a total, with the error-free variant.
- Column profile: spots columns where numbers are stored as text or missing.
Frequently asked questions
- Does an empty cell cause #DIV/0!?
- Yes: in a division, an empty cell counts as zero.