Excel track · Exercise 22 of 86
IF: one test, two answers
Write a decision that returns one of two values, chosen by a single test.
Beginner
Exam results
Read-only data · answer in C2
In C2, write "Pass" if the score in B2 is 70 or more, otherwise "Fail".
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 70 |
Reference answer
What it does
IF takes a test and returns one of two values. The test comes first, the value for a true result second, and the value for a false one third. Either value can be a number, text or another formula. Text needs quote marks, or Excel reads Pass as a name and returns #NAME?.
Ana's score is exactly 70, so =IF(B2>=70,"Pass","Fail") puts Pass in C2. Narrow the comparison to > and the same student fails, because 70 is not greater than 70. The two values are just as easy to reverse, and a reversed pair still fills the column with words, all of them wrong.
Leave the third argument out and a false test returns FALSE, so the cell shows a logical value where a label was expected. Type Pass without quotes and the formula stops with #NAME?.
Syntax
=IF(logical_test, value_if_true, value_if_false)Worked example
On Exam results, =IF(B2>=70,"Pass","Fail") returns Pass.
=IF(B2>=70,"Pass","Fail")Common errors
| Error | What it means and how to fix it |
|---|---|
FALSE | The third argument was left out, so a failing student shows FALSE instead of Fail. |
#NAME? | Pass was written without quotes, so Excel looked for a name and found nothing. |
Swapped branches | With the labels the other way round, every passing student is marked Fail. |
At work
A stock flag or a budget status is an IF underneath, and so is every pass or fail mark on a report.