Excel Help
IF: Let Excel Make Decisions for You
Pass or fail? Free shipping or not? Overdue or on time? The IF function lets Excel answer yes-or-no questions and show the right result, automatically.
Do You Ever Find Yourself Typing the Same Word Again and Again?
Imagine you have exam scores for a class. Anyone with 50 or more passes, everyone else fails. You could read every score and type "Pass" or "Fail" next to it by hand. For 4 students that is fine. For 400, it is a long afternoon, and if a score changes you have to remember to fix the word as well.
The IF function does this for you. You tell Excel the rule once, and it applies it to every row.
You already make decisions like this every day: "If it rains, I take an umbrella. If not, I leave it at home." IF is exactly that, written for Excel.
Is IF Useful for Me?
Probably, if you ever want a cell to say something different depending on a number or a word in another cell:
- Mark scores as Pass or Fail
- Show "Overdue" when a payment is late
- Give a bonus only when sales pass a target
- Show "Low stock" when the quantity gets small
If you catch yourself looking at a column and typing a label by hand, IF can probably do it for you.
See It in Action
Here are four students. Column A has the name and column B the score. In column C we want Excel to write Pass when the score is 50 or more, and Fail otherwise.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Anna | 72 | Pass |
| 3 | Ben | 45 | Fail |
| 4 | Cara | 50 | Pass |
| 5 | Dan | 38 | Fail |
We type one formula in C2 and copy it down. For each row, Excel looks at the score (blue), asks "is it 50 or more?", and writes Pass (green) or Fail (orange).
The cell being checked Answer is yes Answer is no
Notice that Cara scored exactly 50 and passed. That is because our question was "50 or more", not "more than 50". Small details like this matter, and we will look at them below.
The Formula, in Three Simple Pieces
Here is the formula that did it:
=IF( B2>=50, "Pass", "Fail" )
It is a question followed by two possible answers:
What is the question?
B2>=50 means "is the score in B2 greater than or equal to 50?" This is called the test. It can only be true or false.
What if the answer is yes?
"Pass" is shown when the test is true.
What if the answer is no?
"Fail" is shown when the test is false.
Change Ben's score to 60 and his result changes to "Pass" immediately. You never have to touch the formula again.
The Comparison Signs
The question in an IF is usually a comparison. These are the signs you can use:
| Sign | Means | Example |
|---|---|---|
= |
Is equal to | A2="Paid" |
<> |
Is not equal to | A2<>"Paid" |
> |
Is greater than | B2>100 |
>= |
Is greater than or equal to | B2>=50 |
< |
Is less than | B2<10 |
<= |
Is less than or equal to | B2<=10 |
Comma or Semicolon? It Depends on Where You Live
If a formula from the internet gives an error even though it looks perfect, the separator is often the reason.
| Style | Separator | Typical regions | Example |
|---|---|---|---|
| American | Comma , | USA, UK, Canada, Australia | =IF(B2>=50, "Pass", "Fail") |
| European | Semicolon ; | Finland, Sweden, Germany, France, Spain | =IF(B2>=50; "Pass"; "Fail") |
The formula does the same thing in both styles. In many European countries the comma is the decimal mark (2,5 instead of 2.5), so Excel uses the semicolon between the pieces. If you get an error, swap every comma for a semicolon, or the other way around. The examples in this article use commas.
How to Use It Yourself
- Click the cell where you want the result to appear.
- Type =IF(
- Click the cell you want to check, then type the comparison, such as >=50, followed by a comma.
- Type what to show if the answer is yes, in quotation marks if it is a word, then a comma.
- Type what to show if the answer is no, close the bracket ) and press Enter.
- To fill the rest of the rows, double-click the small square in the bottom-right corner of the cell.
=IF(B2>=1000, B2*0.1, 0) gives a 10% bonus on
sales of 1000 or more, and 0 otherwise.
More Than Two Outcomes
What if you want three results, such as Good, Pass and Fail? You can put a second IF inside the "no" part of the first:
=IF(B2>=70, "Good", IF(B2>=50, "Pass", "Fail"))
Excel checks the first question. If the score is 70 or more, it writes "Good". If not, it moves on to the second question. This is called a nested IF. It works well for two or three levels. After that it becomes hard to read, so try not to go deeper than that.
IF with OR: When Either Reason Is Enough
So far our question had only one part. But real life is often more complicated: "We offer free shipping if the order is over 100, or the customer is a member." Either reason is enough.
To ask a question with several parts, you can place the function OR inside the IF.
An example
Here are four orders. Column B has the order total and column C says whether the customer is a member. Column D should say Free if the total is 100 or more, or the customer is a member. Otherwise it says Pay.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Total | Member | Shipping |
| 2 | 1001 | 120 | No | Free |
| 3 | 1002 | 60 | Yes | Free |
| 4 | 1003 | 40 | No | Pay |
| 5 | 1004 | 150 | Yes | Free |
The green cells in columns B and C are the conditions that are true. Order 1001 has only one (the total), order 1002 has only one (membership) and order 1004 has both. All three get free shipping, because one is enough. Order 1003 has neither, so the customer pays.
Here is the formula in D2:
=IF( OR(B2>=100, C2="Yes"), "Free", "Pay" )
The structure is exactly the same as before: a question, then the answer for yes, then the answer for no. The only new thing is that the question (the blue part) is now OR(...), which holds the two conditions separated by a comma.
Reading it out loud
A good way to check a formula is to read it as a sentence: "If the total is at least 100 or the member cell says Yes, show Free. Otherwise show Pay."
OR can have more than two conditions
Just keep adding conditions inside the brackets:
=IF(OR(B2>=100, C2="Yes", D2="Gift card"), "Free", "Pay")
If any one of the three is true, the result is "Free".
OR vs. AND
Its sister function is AND, which is only true when every condition is true. You use it in the same place, inside the IF.
| OR | AND | |
|---|---|---|
| True when | At least one condition is true | All conditions are true |
| Everyday example | "Bring ID or a passport" | "Bring ID and a ticket" |
| Formula | OR(B2>=100, C2="Yes") |
AND(B2>=100, C2="Yes") |
| Order 1002 (60, member) | Free | Pay |
With AND, only order 1004 (150 and a member) would get free shipping. Choosing between OR and AND is just a matter of asking: "Is one reason enough, or do I need all of them?"
The European version
With semicolons, it looks like this:
=IF(OR(B2>=100; C2="Yes"); "Free"; "Pay")
Common Problems and How to Fix Them
You see #NAME?
Most often, a word is missing its quotation marks. Writing
=IF(B2>=50, Pass, Fail) makes Excel look for
something called Pass. Write "Pass" and
"Fail".
Excel says there is a problem with your formula
Check the separators. If your Excel uses European settings, use semicolons (;) instead of commas (,), as explained above. Also count your brackets: every opening bracket needs a closing one. This is easy to get wrong with OR and nested IFs.
The result is FALSE or 0 instead of a word
If you leave out the "no" answer, Excel shows FALSE when the
test is not met. If you want the cell to look empty
instead, write two quotation marks with nothing between
them: =IF(B2>=50, "Pass", "").
The wrong rows pass or fail
Check the boundary. "Greater than" (>) and "greater than or equal to" (>=) give different results for a score of exactly 50. Say the rule out loud and pick the sign that matches.
A number is not recognized
Sometimes a number is stored as text, so it looks like a
number but Excel will not compare it properly. If the cell
has a small green triangle or the number is left-aligned,
convert it to a real number. Also avoid putting quotation
marks around numbers in the test: B2>="50"
compares with text, not with the number 50.
OR gives no error but the wrong answer
Make sure OR is inside the IF, as the first
piece. A formula like =OR(IF(...), ...) does
something completely different. Also, remember that the text
must match what is in the cell: "Yes" will not match "Yes "
with an extra space.
The Short Version
- IF asks a yes-or-no question and shows one result for yes and another for no.
- The formula is =IF(question, result if yes, result if no).
- Words go in quotation marks, numbers and cells do not.
- Put a second IF in the "no" part when you need three outcomes.
- Use OR inside the IF when one reason is enough, and AND when you need all of them.
- Use commas in American-style Excel and semicolons in European-style Excel.