IF function practice exercises, with answers
Ten IF exercises on one sheet, with answers: a pass mark, text results, AND and OR inside IF, a nested IF for grades, and the mistake each one catches.
Here are ten IF exercises on one sheet, with the answer under each one. IF checks a test. It returns one value when the test is TRUE and another when it is FALSE.
Each exercise also shows one mistake. Most IF mistakes give a wrong answer with no error message, so they are easy to miss.
Type this table in A1 to D7. Then type 50 in H2. That is the pass mark. Write each answer in F2, then fill it down to F7.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Name | Score | Days missed | Homework |
| 2 | Aiko | 72 | 1 | Yes |
| 3 | Ben | 49 | 4 | No |
| 4 | Chloe | 50 | 9 | Yes |
| 5 | Dev | 88 | 0 | Yes |
| 6 | Elif | 91 | 6 | No |
| 7 | Farid | 65 | 2 | Yes |
Every exercise uses the same shape:
=IF(test, value_if_true, value_if_false). The answers list the
results in F2 to F7, from Aiko down to Farid.
Exercise 1 — a pass mark
A score of 50 or more is a pass. Show Pass or Fail.
Answer: =IF(B2>=50,"Pass","Fail") →
Pass, Fail, Pass, Pass, Pass, Pass
Look at Chloe. She scored exactly 50. Type > instead of
>= and she fails. "50 or more" means
>=. "More than 50" means >.
Exercise 2 — the pass mark in a cell
Do the same test, but read the pass mark from H2.
Answer: =IF(B2>=$H$2,"Pass","Fail") →
Pass, Fail, Pass, Pass, Pass, Pass
The dollar signs lock H2. Without them, the copy in F3 reads H3, which is empty. Excel reads an empty cell as 0 here. So Ben passes with 49, and there is no error.
Now change H2 to 60. Chloe now fails, and you did not touch the formula. That is why the number goes in a cell.
Exercise 3 — a number as the result
A student with no days missed gets 5 extra points. Show the score plus 5, or the score as it is.
Answer: =IF(C2=0,B2+5,B2) →
72, 49, 50, 93, 91, 65
Only Dev missed no days, so only his score changes. Do not put quote marks around
B2+5. With them, Dev's cell shows the text B2+5, not 93.
Text goes in quotes. Numbers and sums do not.
Exercise 4 — the missing third part
What does =IF(B3>=50,"Pass") show for Ben?
Answer: FALSE
With no third argument, IF shows FALSE when the test fails. FALSE is not a word you chose, and
it is not an empty cell. Always write the third argument, even when it is 0 or
"".
Exercise 5 — show nothing
Show Retake for a student who failed. Show nothing for everyone else.
Answer: =IF(B2>=50,"","Retake") →
Retake for Ben. The other 5 cells look empty.
Two quote marks with nothing between them give empty text. The cell looks empty, but it holds a
formula. So COUNTA still counts it.
Exercise 6 — test a word
Show Done if the homework is in. Show Chase if it is not.
Answer: =IF(D2="Yes","Done","Chase") →
Done, Chase, Done, Done, Chase, Done
The word Yes needs quote marks in the test too. Without them, Excel looks for a name called Yes
and shows #NAME?. The test ignores capitals, so
"yes" works the same.
Exercise 7 — AND inside IF
A pass now needs two things: a score of 50 or more, and the homework in.
Answer:
=IF(AND(B2>=50,D2="Yes"),"Pass","Fail") →
Pass, Fail, Pass, Pass, Fail, Pass
Elif has the top score and still fails, because her homework is missing. AND is TRUE only when every test is TRUE.
Type OR by mistake and Elif passes. Before you type, say the rule out loud with the word "and" or the word "or".
Exercise 8 — OR inside IF
The teacher wants to talk to anyone who scored under 50 or missed more than 5 days. Show Talk or OK.
Answer:
=IF(OR(B2<50,C2>5),"Talk","OK") →
OK, Talk, Talk, OK, Talk, OK
OR is TRUE when any one test is TRUE. Each test is a full comparison, with its own cell and its
own sign. OR(B2<50,>5) is not a formula, and Excel will not
accept it.
Exercise 9 — a nested IF for grades
85 or more is an A. 70 or more is a B. 50 or more is a C. Anything lower is an F.
Answer:
=IF(B2>=85,"A",IF(B2>=70,"B",IF(B2>=50,"C","F"))) →
B, F, C, A, A, C
The second IF goes where the first IF's FALSE value would be. Excel checks the tests in order and stops at the first TRUE. So start with the highest grade.
Write the tests from lowest to highest and the first test catches everyone. Dev gets a C for 88, and there is no error.
Exercise 10 — the same grades with IFS
Write the grades again with IFS. It needs no IF inside an IF.
Answer:
=IFS(B2>=85,"A",B2>=70,"B",B2>=50,"C",TRUE,"F") →
B, F, C, A, A, C
IFS takes pairs: a test, then its value. The last pair, TRUE and "F", means "anything else".
Leave it out and Ben shows #N/A, because no test is TRUE for him.
IFS needs Excel 2019 or later, or Microsoft 365. In older versions, use the nested IF.
What goes in quote marks
| In the formula | Example | Quotes? |
|---|---|---|
| A word as the result | "Pass" | Yes |
| A word in the test | D2="Yes" | Yes |
| Nothing | "" | Yes, two |
| A number | 50 | No |
| A cell | $H$2 | No |
| A sum | B2+5 | No |
Questions people ask
How many IFs can I put inside each other?
Excel allows 64. But after three or four, a nested IF is hard to read and hard to change. Use IFS, or a small table of grades with an approximate VLOOKUP. The VLOOKUP practice exercises show how.
What does NOT do inside IF?
NOT turns TRUE into FALSE, and FALSE into TRUE. So
=IF(NOT(D2="Yes"),"Chase","") shows Chase for Ben and Elif. The
test D2<>"Yes" does the same job. Use the one that is easier
to read.
Why does my IF say FALSE when the test looks TRUE?
Often a number is saved as text. Then B2=50 is FALSE, even when B2
shows 50. Check it with =ISNUMBER(B2). An extra space in a word
does the same thing to a text test.
Can I check my IF formulas somewhere?
Yes. Each lesson is a real sheet you type into. Your answer is checked when you press Enter. The page on how your formula is checked says what happens then.