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

By Ali, Founder, Excel Lessons

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

Conversion rate is sales divided by leads.

In `D2`, type:

=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

Conversion rate is sales divided by leads. In D2, type: =C2/B2.

Hint. C2 over B2.
2

Social had no leads, so B3 is 0. In D3, guard the division with IF. Shape: =IF(test, if_true, if_false). Show 0 when B3 is 0, otherwise divide C3 by B3.

Hint. Test the leads for zero first.
3

In D4, write the same guarded IF for Search. This shape fills down the whole column.

Hint. Same shape as D3.
4

Print reports 2 sales and no leads, which cannot be right. Read =IF(B6=0,0,C6/B6) in D6 and say what it shows.

Hint. What happens when the test is TRUE?
5

D6 has an IFERROR that shows 0. That hides the problem instead of naming it. Fix D6 with an IF. Show the text Check data when B6 is 0, and the rate otherwise. Remember: IF names the condition, IFERROR only hides it.

Hint. The middle argument is the text, in quotes.