The #DIV/0! guide
#DIV/0! means the formula divided by zero, or by an empty cell, which Excel counts as zero. Test the number you divide by first: =IF(C2=0, "", B2/C2) leaves the cell empty when there's nothing to divide by.
Where it comes from
- A divisor that is 0: a month the shop was closed, a rep with no calls.
- A divisor that is empty: months not filled in yet. An empty cell counts as 0 in arithmetic.
AVERAGEof a range with no numbers, andAVERAGEIForAVERAGEIFSwhen no row matches: there is nothing to divide by.
The fix: test the divisor
=IF(C2=0, "", B2/C2)
When C2 is 0 or empty, the cell shows nothing; otherwise it divides. =IF(C2, B2/C2, "") does the same, because 0 counts as FALSE. Test the cell you divide by, C2, not the one on top: a month with revenue from a late payment and no orders would break again.
IFERROR, or IF?
=IFERROR(B2/C2, "") works too, and it's shorter. It also hides every other error in the formula: a typo in a reference, text in the revenue column. IF only handles the zero you expected, so a real mistake still shows up. Use IFERROR when you've checked the formula and only want to tidy the output.
Empty, not 0
Replacing the error with 0 looks tidy, but 0 is a value. It says August's average order was worth $0, it pulls down an average of the months, and a chart draws a dip to the floor. "" leaves the cell empty, so AVERAGE skips it and a line chart leaves a gap.
Common mistakes
- Fixing one cell. Fix the formula in the first row and fill it down, so every month gets the same fix.
- Testing the wrong cell.
IF(B2=0, …)tests the revenue, but the division is by the orders. - A text placeholder like "-" or "n/a". It reads fine, but SUM and AVERAGE skip it while + and * turn it into #VALUE!.
More errors and their fixes: #VALUE!, #NAME?, #REF!, IFERROR.
FAQ
- How do I get rid of #DIV/0! in Excel?
- Test the divisor first: =IF(C2=0,"",B2/C2). The cell stays empty when C2 is 0 or blank. =IFERROR(B2/C2,"") also works but hides every other error too.
- Why does dividing by an empty cell give #DIV/0!?
- In arithmetic, Excel treats an empty cell as 0, so B2/C2 with C2 empty is a division by zero.
- Should I show 0 or a blank instead of #DIV/0!?
- Usually a blank (""). A 0 is a real value: it lowers averages and shows as a drop in charts.
- Why does AVERAGEIF return #DIV/0!?
- No cell matched the condition, so there is nothing to average. Check the criterion for typos, or wrap it in IFERROR.



































































