#DIV/0! error in Excel: what it means and how to fix it

#DIV/0! means a formula divided by zero. Nobody types =5/0 on purpose: the zero almost always comes from a cell, usually one that is empty. Excel counts an empty cell as zero in maths, so a rate, an average or a share copied down a column shows #DIV/0! on every row whose data hasn’t arrived yet. The fix is to decide what that row should show, and test for it.

1. Dividing by an empty cell or a zero

The sign-up rate is sign-ups divided by visits. Email’s rate works; the podcast hasn’t had any visits yet, so C3/B3 divides by an empty cell:

D3fx=C3/B3

Row	A	B	C	D
1	Channel	Visits	Sign-ups	Rate
2	Email	400	18	4.5%
3	Podcast			#DIV/0!

Test the divisor first, and only divide when it isn’t zero. An empty cell passes the test B3=0 too:

To leave the cell looking empty instead, return "": =IF(B3=0,"",C3/B3). Bear in mind that "" is text: a later formula that adds or multiplies that cell with + or * will show #VALUE!.

2. An average of nothing

AVERAGE adds up the numbers and divides by how many there are. With no numbers in the range, that count is zero. AVERAGEIF and AVERAGEIFS do the same when no row meets the condition. There are no North deals here:

E2fx=AVERAGEIF(A2:A4,D2,B2:B4)

Row	A	B	C	D	E
1	Region	Deal		Find	Average
2	East	$1,200		North	#DIV/0!
3	West	$800
4	East	$1,500

Count the matches first, and average only when there are some:

3. MOD and QUOTIENT by zero

MOD returns the remainder after division, and QUOTIENT the whole-number part. Both divide, so both show #DIV/0! when the divisor is zero or empty. The same IF test fixes them: =IF(B2=0,0,MOD(A2,B2)).

Practise it: cost per sale, or “No sales”

Your task: In D2, work out the cost per sale: the spend in B2 divided by the sales in C2. When C2 is 0 or empty, show the text "No sales" instead.

Graded live

D2fx

Row	A	B	C	D
1	Campaign	Spend	Sales	Cost per sale
2	Spring	$1,200.00	48

Write the formula in the bar above, then press Check or Enter.

Hint

Divide B2 by C2, but test C2 with IF first: when C2=0 (an empty cell counts as 0), return "No sales".

Solution

=IF(C2=0,"No sales",B2/C2) Any formula that returns the same result passes.

Check

IF or IFERROR?

IFERROR, as in =IFERROR(C3/B3,0), also returns 0 for the empty podcast row, and it’s shorter. But IFERROR catches every error, not only division by zero: if the visits cell held the text “n/a”, or the formula pointed at a deleted cell, IFERROR would still show 0 and you’d never know. The IF test changes only the case you meant. Use IFERROR when any error should give the same fallback, and you’ve checked the formula works.