Guarding a Division: IF or IFERROR?

About this lesson

A division by zero in Excel returns #DIV/0!. The cleanest guard is an IF that tests the divisor first. It names the condition, and it can say something useful when it happens.

By the end you can

guard a division with IF so a zero never becomes #DIV/0!.
say when a fallback of 0 hides a real problem and what to show instead.

Practises IF IFERROR ISERROR

The idea

Two ways to stop a #DIV/0!. IFERROR catches the error after it happens and swaps in a fallback. IF tests the divisor before dividing and never lets the error happen at all. They give the same number on a normal row. They differ on the row that matters. IF knows why it took the fallback branch, so it can say Check data instead of 0.

The mistake this lesson is built around is a fallback of 0. A channel with no leads and a channel with two sales and no leads both show 0. The second one is a data error that the guard has made invisible. A guard's job is to stop the error spreading, not to stop anybody noticing.

The mistake to watch for

A fallback of 0. A channel with no leads and a channel with two sales and no leads both show 0. The second is a data error the guard has made invisible. A guard's job is to stop the error spreading, not to stop anybody noticing. Name the condition with IF, and make the fallback say what happened.

Where this comes up again

TRUE, FALSE and the Question Behind Them Logic
The Six Comparisons Logic
IF Logic
AND, OR and NOT Logic

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, the email channel's conversion rate: sales divided by leads.

To begin, type it exactly:

=C2/B2

Row	A	B	C	D
1	Channel	Leads	Sales	Rate
2	Email	250	30
3	Social	0	0
4	Search	180	27
5	Referral	40	0
6	Print	0	2
7
8

Every step

1

In D2, the email channel's conversion rate: sales divided by leads. To begin, type it exactly: =C2/B2.

Hint. C2 over B2.
2

Social had no leads. In D3, guard the division with IF: if the leads are 0, show 0, otherwise divide as normal.

Hint. Test the leads for zero first.
3

In D4, the same guarded formula for Search, the shape that fills down the whole column.

Hint. Same shape as D3.
4

Print reports two sales and no leads, which cannot be right. D6 holds =IF(B6=0,0,C6/B6). What does it show?

Hint. What happens when the test is TRUE?
5

D6 has been changed to an IFERROR that also shows 0, and it hides the same problem. Replace it with an IF. Show the text Check data when the leads are 0, and the rate otherwise.

Hint. The middle argument becomes the text.