Text results without quotes
=IF(B2>=50,Pass,Fail)
The checker says: Text in a formula needs double quotes: "Pass".
Results that are text need quotes. Numbers don’t.
Excel function
The IF function in Excel
IF checks a condition, then returns one value when it’s true and another when it’s false. It’s how a sheet makes decisions.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
| Argument | What it does |
|---|---|
| logical_test | A comparison that is either TRUE or FALSE, such as B2>=50. |
| value_if_true | What to show when the test is TRUE. Text goes in quotes: "Pass". |
| value_if_false | Optional. What to show when the test is FALSE. Leave it out and IF shows FALSE. |
Here’s what that formula is doing.
=IF(B2>=50,"Pass","Fail") shows Pass for a score of 50 or more and Fail for anything lower. Filled down this column of scores, only Farah’s 47 fails.
Try it with real data.
Type a formula, then check it. Hints step in when you’re stuck, and the last one shows the answer.
Example data
Task 1
Delivery is free for orders of 100 or more, otherwise it costs 6.50. In C2, work out the delivery charge.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Total | Delivery |
| 2 | Order 1041 | 86.40 | (answer cell) |
Scroll the sheet sideways to see every column.
Nice. Try the next task
Example data
Task 2
In C2:C9, show Pass for a score of 50 or more, otherwise Fail.
Write the formula for C2. We’ll fill it down to C9 for you.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Amara | 78 | (answer cell) |
| 3 | Ben | 64 | (answer cell) |
| 4 | Chen | 91 | (answer cell) |
| 5 | Dina | 55 | (answer cell) |
| 6 | Eli | 83 | (answer cell) |
| 7 | Farah | 47 | (answer cell) |
| 8 | Gus | 70 | (answer cell) |
| 9 | Hana | 88 | (answer cell) |
Scroll the sheet sideways to see every column.
That’s both. Practise the whole course free.
Common mistakes, and what our checker says.
Each message below is the checker’s real reply to that formula on the example data.
> instead of >=
=IF(B2>50,"Pass","Fail")
The checker says: Check the boundary. Use >= so the value itself counts too. > leaves it out.
> leaves out 50 itself, so a score of exactly 50 would fail.
A missing third argument
=IF(B2>=50,"Pass")
The checker says: IF needs a third argument for when the test isn’t met. Without it, the cell shows FALSE.
Farah’s 47 shows FALSE instead of Fail.
A number in quotes
=IF(B2>=100,0,"6.50")
The checker says: Your formula returns the text "6.50", not a number. Numbers don’t need quotes in a formula.
In quotes, 6.50 becomes text, and SUM skips text, so totals come out wrong.
Questions
How do I check more than one condition?
Put AND or OR inside the test: =IF(AND(B2>=50,C2="Yes"),"Pass","Fail"). For three or more outcomes, nest IFs or use IFS.
Can IF leave a cell blank?
Use empty quotes: =IF(B2>=50,"Pass",""). The cell looks empty, though it holds empty text.
Does IF work with text?
Yes: =IF(D2="Yes","Paid","Due"). Text comparisons in Excel aren’t case-sensitive.
IF is part of module 5 of Excel Foundations: Make decisions.
Start practising now.
Excel Foundations is free, with no sign-up. Your progress is saved in this browser.