=IF(B2>=50,"Pass","Fail") checks whether the score in B2 is 50 or more. If it is, the cell shows Pass; if it is not, it shows Fail.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 72 | Pass |
| 3 | Ben | 45 | Fail |
| 4 | Chloe | 50 | Pass |
| 5 | Dan | 38 | Fail |
| 6 | Eve | 91 | Pass |
Click C2 to see the formula, then change Ben's score in B3 to 60: his result turns to Pass. Chloe has exactly 50, and >= means "50 or more", so she passes. With > she would fail.
IF syntax
=IF(logical_test, value_if_true, value_if_false)
logical_testis any comparison or formula that gives TRUE or FALSE:B2>=50,C2="Paid",D2<TODAY().value_if_trueis what the cell shows when the test is TRUE.value_if_falseis what it shows otherwise. It is optional: leave it out and the cell shows FALSE when the test fails. Keep the comma but leave the argument empty,=IF(B2>=50,"Pass",), and it shows 0.
Each result can be text in double quotes ("Pass"), a number (0), a cell reference (C2), or another formula. Text without quotes is read as a name, so =IF(B2>=50,Pass,Fail) gives #NAME?.
The comparison operators are = (equal), <> (not equal), >, <, >= and <=. In Excel set to a language that uses a decimal comma, arguments are separated by semicolons: =IF(B2>=50;"Pass";"Fail"). Google Sheets uses the same IF with the same arguments.
IF with text
A test can compare a cell with text. The text goes in double quotes, and the comparison ignores upper and lower case.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Free delivery | Exact case |
| 2 | 1001 | North | Yes | Yes |
| 3 | 1002 | South | No | No |
| 4 | 1003 | north | Yes | No |
| 5 | 1004 | East | No | No |
| 6 | 1005 | NORTH | Yes | No |
Column C says Yes for North, north and NORTH, because = does not care about case. When case matters (product codes, passwords), test with EXACT instead, as in column D: only the row that reads North exactly gets Yes.
To test for anything except a value, use <>: =IF(B2<>"North","Standard","Free"). To test whether a cell contains a word somewhere inside it, IF needs SEARCH, which is covered on the IS functions page.
IF with a calculation
The results of IF do not have to be text. Here each rep earns a 5% bonus on sales over 1,000, and nothing below that:
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Bonus |
| 2 | Ana | $1,400 | $70 |
| 3 | Ben | $800 | $0 |
| 4 | Chloe | $1,000 | $0 |
| 5 | Dan | $2,600 | $130 |
| 6 | Eve | $1,150 | $58 |
Ana gets 0. Chloe sold exactly 1,000, and the test is "greater than 1,000", so she also gets 50.
The test can also compare two cells, =IF(B2>C2,"Over target","Under target"), or a cell with a calculation, =IF(B2>AVERAGE($B$2:$B$6),"Above average","").
IF a cell is blank
B2="" is TRUE when B2 is empty. Use it to skip rows that have no data yet, so the formula column does not fill up with zeros:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total | Status |
| 2 | Pens | 10 | $1.50 | $15.00 | Ordered |
| 3 | Paper | $4.20 | Not ordered | ||
| 4 | Ink | 2 | $18.00 | $36.00 | Ordered |
| 5 | Tape | $2.80 | Not ordered | ||
| 6 | Clips | 5 | $0.90 | $4.50 | Ordered |
"" (two double quotes with nothing between them) is empty text, so Paper and Tape show nothing in column D. Type a quantity in B3 and the total appears. Column E tests the opposite with <>"", "is not blank".
A cell holding ="" looks empty but is not blank to ISBLANK. The difference matters when other formulas read the column, for example COUNTA counts it.
IF with two conditions
IF takes one test. To require two things at once, put AND inside the test; to accept either of two, use OR:
=IF(AND(B2>=50,C2>=50),"Pass","Fail")
=IF(OR(B2="North",B2="South"),"Zone 1","Zone 2")
Both are explained with live examples on the AND and OR page. For three or more possible results (A, B, C, F), nest one IF inside another (nested IF) or use IFS.
Practice: reorder or not
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Stock | Minimum | Status |
| 2 | Pens | 12 | 20 | |
| 3 | Paper | 30 | 25 | |
| 4 | Tape | 15 | 12 | |
| 5 | Ink | 8 | 10 |
Your turn: In D2, show "Reorder" when the stock in B2 is below the minimum in C2, otherwise show "OK". The formula fills down to D5.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Bonus |
| 2 | Ana | $1,250 | |
| 3 | Ben | $600 | |
| 4 | Chloe | $2,300 |
Your turn: In C2, return a bonus of 10% of the sales in B2 when the sales are over 1000, and 0 otherwise. The formula fills down to C4.
Common IF mistakes
Quotes around a number. =IF(B2>"50","High","Low") compares B2 with the text "50", not the number 50. Excel ranks every number below every piece of text, so the test is FALSE for any number in B2 and the formula always says Low. Write numbers without quotes: B2>50.
Using ==. Excel's equality test is a single =. Typing =IF(B2=="Paid","Yes","No") makes Excel refuse the formula with "There's a problem with this formula".
Numbers stored as text. If B2 holds '12 (text that looks like a number, often from an import), B2>=50 compares text with a number. Text always counts as larger, so the test is TRUE and 12 passes. Convert the column first; the text to number page shows how.
Expecting TRUE as text. =IF(B2>=50,"TRUE","FALSE") returns the words as text, which other formulas cannot use as logical values. If you want TRUE and FALSE, you do not need IF at all: =B2>=50 returns them directly.
Missing the closing parentheses. Each IF opens one bracket and needs its own ). While you edit, Excel shows each pair of brackets in its own colour, which makes a missing one easy to spot.
Frequently Asked Questions
How do I write an IF THEN formula in Excel?
Write =IF(test, value_if_true, value_if_false). For example =IF(B2>=50,"Pass","Fail") shows Pass when B2 is 50 or more and Fail otherwise. Text results go in double quotes, numbers and cell references do not.
Why does my IF formula return FALSE?
You left out the third argument. =IF(B2>=50,"Pass") returns FALSE when the test fails. Add a value for that case, for example "" to show an empty cell: =IF(B2>=50,"Pass","").
Is the IF function case-sensitive?
No. =IF(B2="North","Yes","No") returns Yes for North, north and NORTH. To match the exact case, use EXACT as the test: =IF(EXACT(B2,"North"),"Yes","No").
How do I make IF leave a cell blank?
Return an empty text string, two double quotes: =IF(B2="","",B2*C2) shows nothing when B2 is empty and the product otherwise. The cell still holds a formula, so ISBLANK on it returns FALSE.
Can IF check more than one condition?
Yes. Put AND or OR inside the test, =IF(AND(B2>=50,C2>=50),"Pass","Fail"), or nest one IF inside another for more than two outcomes. In Excel 2019 and later, IFS handles several outcomes without nesting.
