Excel practice › Excel functions › IF
IF in Excel: how it works, with examples you can edit
IF checks a condition and returns one value when it is true and another when it is false. To label a customer by the spend in C2: =IF(C2>=2000, "Key account", "Standard").
The condition is usually a comparison. Each of the two results can be text in quotes, a number, a calculation, or another IF.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
logical_test- The condition: anything that comes out TRUE or FALSE, like
C2>=2000orB2="Yes". value_if_true- What to return when the condition is true.
value_if_false- What to return when it is false. Optional in the syntax, but write it every time. For a cell that looks empty, use two quotes with nothing between them:
"".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Type |
| 2 | Brightline Co | 14 | 6200 | Key account |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
2000 to 7000 and the cell switches to Standard.The comparisons
The condition compares two things with one of six operators:
=equal to,<>not equal to>greater than,>=greater than or equal to<less than,<=less than or equal to
The one to watch is the boundary. "2000 or more" is >=2000. "More than 2000" is >2000, and a customer at exactly 2000 lands on the other side.
To compare with text, put the text in quotes: B2="Yes". The words TRUE and FALSE are the only ones Excel understands without quotes.
Text results and number results
The results follow the same rule: text goes in quotes, numbers don't. A result can also be a calculation. This one pays a 5% credit on the spend, but only to customers with 10 or more orders:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Credit |
| 2 | Brightline Co | 14 | 6200 | 310 |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
0 at the end has no quotes: it is a number, and a total of this column will count it.A number in quotes is text. "0" or "50" looks like a number in the cell, but SUM ignores text values, so a total of that column comes out too low. If the result is an amount, leave the quotes off.
Nested IF: more than two outcomes
For three outcomes, put a second IF where the "false" result goes. Excel checks the conditions in order and stops at the first one that is true, so the order matters. This formula is meant to give Gold from 5000, Silver from 2000 and Bronze below that. Brightline spent 6200:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Tier |
| 2 | Brightline Co | 14 | 6200 | Silver |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
=IF(C2>=5000,"Gold",IF(C2>=2000,"Silver","Bronze")).Excel 2019 and later also have IFS, which takes the same tests as a flat list: =IFS(C2>=5000,"Gold",C2>=2000,"Silver",TRUE,"Bronze"). It returns the value for the first condition that is true, so the order matters in the same way. The final TRUE is the catch-all: without it, a row that passes no test gives #N/A. The sheets on this page don't run IFS, so use nested IF in the exercises.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
A customer is active with 5 or more orders this year. In D2, return the text Active or Inactive for Brightline, from its number of orders in B2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Status |
| 2 | Brightline Co | 14 | 6200 | |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
Show the answer
=IF(B2>=5,"Active","Inactive") gives Active.
B2>=5 is true for 5 orders and up. IF returns the first text when it is true and the second when it is false.
Customers who spent more than 2000 get a rebate of 3% of their spend. Everyone else gets 0. In D2, calculate the rebate for Brightline as a number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Rebate |
| 2 | Brightline Co | 14 | 6200 | |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
Show the answer
=IF(C2>2000,C2*0.03,0) gives 186.
The "true" result is a calculation, C2*0.03, and the "false" result is the number 0 without quotes. 6200 × 0.03 = 186.
Tiers by spend: Gold from 5000, Silver from 2000, Bronze below 2000. In D2, return the tier for the spend in C2 with one formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Orders | Spend | Tier |
| 2 | Brightline Co | 14 | 6200 | |
| 3 | Nolan Sons | 3 | 950 | |
| 4 | Cedar Clinic | 9 | 2400 | |
| 5 | Harlow Print | 6 | 1980 | |
| 6 | Vista Gym | 21 | 8750 | |
| 7 | Juniper Cafe | 1 | 120 |
Show the answer
=IF(C2>=5000,"Gold",IF(C2>=2000,"Silver","Bronze")) gives Gold.
The highest tier is tested first. A spend that fails >=5000 goes on to the second IF, which splits what is left into Silver and Bronze.
Questions people ask
How do I use IF with 3 conditions in Excel?
Three outcomes need two tests: put a second IF in the place of the first one's "false" result, as in =IF(C2>=5000,"Gold",IF(C2>=2000,"Silver","Bronze")). Test the most demanding condition first, because Excel stops at the first test that is true. In Excel 2019 and later, IFS does the same with a flat list of tests.
How do I write an IF formula with text?
Put every piece of text in double quotes, in the condition and in the results: =IF(B2="Yes","Approved","Pending"). Numbers and cell addresses take no quotes. TRUE and FALSE are the only words Excel understands without them.
Can IF check two conditions at once?
Yes, with AND or OR as the test: =IF(AND(B2>=5,C2>=2000),"Yes","No") needs both to be true, and OR needs one. See AND, OR and NOT.
How do I make IF return a blank cell?
Use two double quotes with nothing between them as the result: =IF(C2>=2000,"Key account",""). The cell then shows nothing when the condition is false.