The IF function: a simple worked example
IF checks whether something is true, then shows one result if it is and a different result if it isn't. For example: if someone has done at least 12 hours of training, show Yes, otherwise show No.
A worked example
Everyone in a team needs at least 12 hours of training this year. Here's where they are so far.
| A | B | |
|---|---|---|
| 1 | Employee | Hours |
| 2 | Grace | 14 |
| 3 | Tom | 9 |
| 4 | Tanya | 12 |
Go down the list and ask the same question each time: is the number of hours 12 or more?
- Grace has 14. That's 12 or more, so Yes.
- Tom has 9. That's less than 12, so No.
- Tanya has exactly 12. "At least 12" includes 12, so Yes.
IF asks that question on every row for you.
The formula
=IF(B2>=12,"Yes","No")
B2>=12is the question: is B2 greater than or equal to 12? It's either true or false."Yes"is what to show when it's true."No"is what to show when it's false.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Hours | Met target? |
| 2 | Grace | 14 | Yes |
| 3 | Tom | 9 | No |
| 4 | Tanya | 12 | Yes |
The two results don't have to be words. They can be numbers or other formulas, such as a bonus only for people over target: =IF(B2>=12,50,0). Use "" to leave the cell looking blank.
The comparisons you can use
| Type | Means | Example |
|---|---|---|
= | equal to | C2="Sales" |
<> | not equal to | C2<>"Sales" |
> | more than | B2>12 |
>= | at least (more than or equal to) | B2>=12 |
< | less than | B2<12 |
<= | at most (less than or equal to) | B2<=12 |
Read the wording carefully. "More than 12" is >12, so someone on exactly 12 gets No. "At least 12" is >=12, so they get Yes.
Common mistakes
Text without quotes
Words in a formula need double quotes: "Yes", not Yes. Without them Excel looks for something called Yes, can't find it, and shows #NAME?.
Numbers in quotes
The opposite mistake: =IF(B2>="12","Yes","No"). "12" in quotes is text, not a number, and Excel never counts a number as bigger than text, so everyone gets No. Leave numbers without quotes.
Typing ≥ or ≤
Excel doesn't understand the ≥ and ≤ symbols. Type two characters instead: >= and <=.
Typing the target into every formula
If the target might change, put it in a cell (say F1) and refer to that: =IF(B2>=$F$1,"Yes","No"). Then you change one cell, not every formula. The $ signs keep the reference on F1 when you copy the formula down.
More than two results
For three or more results, such as Gold, Silver and Bronze, use IFS. It checks each question in order and stops at the first one that's true, so put the highest band first:
=IFS(B2>=20,"Gold",B2>=12,"Silver",TRUE,"Bronze")
The last pair, TRUE,"Bronze", catches everyone else. IFS is in Excel 2019 onwards and Google Sheets. In older versions, put one IF inside another: =IF(B2>=20,"Gold",IF(B2>=12,"Silver","Bronze")).
Try it now
The training target is at least 12 hours. In C2, show Yes if the employee has met it and No if they haven't. Your formula will be copied down to C8.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Hours | Met target? |
| 2 | Priya | 12 | |
| 3 | Grace | 14 | |
| 4 | Tom | 9 | |
| 5 | Marek | 21 | |
| 6 | Nia | 11 | |
| 7 | Ollie | 16 | |
| 8 | Lucy | 3 |