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.
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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Score | Result | Refresher? | Marks to pass | Pass mark | 40 | |
| 2 | Grace | 52 | No | 0 | ||||
| 3 | Tom | 38 | No | 2 | ||||
| 4 | Priya | 61 | No | 0 | ||||
| 5 | Marek | 40 | No | 0 | ||||
| 6 | Nia | 27 | Yes | 13 | ||||
| 7 | Ollie | 45 | No | 0 | ||||
| 8 | Lucy | 33 | No | 7 | ||||
| 9 | Hamza | 70 | No | 0 | ||||
| 10 | Rachel | 19 | Yes | 21 | ||||
| 11 | Sunil | 48 | No | 0 |
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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Hours | Overtime? | Extra hours | Contract hours | 37 | |
| 2 | Amira | 40 | |||||
| 3 | Ben | 37 | |||||
| 4 | Cara | 30 | |||||
| 5 | Dev | 45 |
=IF(in . Start with =IF(.=IF(B2>$G$1in . First the test: did they work more than the contract hours in G1? G1 is locked, since every row uses it.=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.=IF(B2>$G$1,"Yes","No")in . Then what to show if it's false. Amira worked 40, more than 37, so Yes.=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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Hours | Overtime? | Extra hours | Contract hours | 37 | |
| 2 | Amira | 40 | |||||
| 3 | Ben | 37 | |||||
| 4 | Cara | 30 | |||||
| 5 | Dev | 45 |
=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
- 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.
- Anyone scoring below 30 has to book a refresher course. In D2, show Yes or No.
- In E2, show how many more marks each person needed to pass. Show 0 if they passed.
- 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.