Confident Cells

IF: practice exercises

Making a decision on every row. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

These are scores from a compliance test, and the pass mark is in H1. In C2, show Pass if the score is at least the pass mark, otherwise Fail.

ABCDEFGH
1EmployeeScoreResultRefresher?Marks to passPass mark40
2Grace52No0
3Tom38No2
4Priya61No0
5Marek40No0
6Nia27Yes13
7Ollie45No0
8Lucy33No7
9Hamza70No0
10Rachel19Yes21
11Sunil48No0

How it works

IF(test, value if true, value if false) checks something, then gives one result or the other.

Comparisons: = <> (not equal) < > <= >=. Put text results in "quotes". Numbers and cell references don't need them.

=IF(B2>$G$1,"Yes","No") =IF(B2>$G$1,B2-$G$1,0)

What IF does

IF checks something, then gives one result if it's true and another if it's false: IF(test, value if true, value if false).

The test is a comparison: = equal, <> not equal, < less than, > more than, <= at most, >= at least.

Text results go in "quotes". Numbers, cell references and calculations don't need them.

Worked example

ABCDEFG
1NameHoursOvertime?Extra hoursContract hours37
2Amira40
3Ben37
4Cara30
5Dev45
  1. =IF( in . Start with =IF(.
  2. =IF(B2>$G$1 in . First the test: did they work more than the contract hours in G1? G1 is locked, since every row uses it.
  3. =IF(B2>$G$1,"Yes" in . After a comma, what to show if the test is true. It's text, so it goes in quotes.
  4. =IF(B2>$G$1,"Yes","No") in . Then what to show if it's false. Amira worked 40, more than 37, so Yes.
  5. =IF(B2>$G$1,"Yes","No") in . Ben worked exactly 37. 37 > 37 is false, so No. If 37 should count, the test would be >=.

IF can calculate too

ABCDEFG
1NameHoursOvertime?Extra hoursContract hours37
2Amira40
3Ben37
4Cara30
5Dev45
  1. =IF(B2>$G$1,B2-$G$1,0) in . The results don't have to be text. Here the true result is a calculation, the hours over contract, and the false result is 0. Amira: 40 − 37 = 3.

What you'll practise

  1. These are scores from a compliance test, and the pass mark is in H1. In C2, show Pass if the score is at least the pass mark, otherwise Fail.
  2. Anyone scoring below 30 has to book a refresher course. In D2, show Yes or No.
  3. In E2, show how many more marks each person needed to pass. Show 0 if they passed.
  4. Anyone who sells at least the target number of units in H1 earns the bonus in H2 for every unit they sold. Anyone below the target gets nothing. In C2, work out each person's bonus.