FormuladrillIF
IF(logical_test, value_if_true, value_if_false) checks a condition and returns one value when it is true and another when it is false. For example =IF(B2>=50, "Pass", "Fail"). IFs can be nested, but IFS is clearer for more than two or three outcomes.
Syntax
IF(logical_test, value_if_true, [value_if_false])| Part | What it is |
|---|---|
logical_test | Anything that is TRUE or FALSE: B2>=50, A2="North", AND(...), ISBLANK(A2). |
value_if_true | What to return when the test is TRUE. Text goes in quotes. |
value_if_false | What to return otherwise. If omitted, Excel returns FALSE. |
Examples
=IF(B2>=50, "Pass", "Fail")Result: Pass when B2 is 50 or more, otherwise Fail
=IF(A2="", "", A2*1.1)Result: Empty text when A2 is blank, otherwise A2 plus 10%
Keeps the column clean when fill-down reaches empty rows.
=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))Result: A for 90 and above, B for 80 to 89, C for 70 to 79, otherwise F
Nested IF: tests run top to bottom, so put the highest bar first.
=IF(AND(A2="North", B2>1000), B2*0.05, 0)Result: A 5% bonus only when both conditions hold
Common mistakes
- The cell shows FALSE: value_if_false was left out. Add the third argument, even if it is "".
- Text is not quoted: =IF(B2>=50, Pass, Fail) gives #NAME?. Write "Pass" and "Fail".
- Nested IF returns the wrong grade because the tests are in the wrong order; with >= tests, go from the highest threshold down.
- Comparing a number with text: B2>="50" is text comparison and gives wrong results. Compare with the number 50.
Practice IF
These drills check your answer on live data, free, no account needed.
- Pass or failA score of at least the pass mark in G1 is a "Pass", anything lower is a "Fail". Fill C2 and C3.
- Letter gradesTurn the score into a grade: 90 or more is A, 80 to 89 is B, 70 to 79 is C, anything lower is F. Fill C2 and C3.
- Bonus for the northA rep earns a bonus of 5% of sales only if sales are at least 50,000 AND the region is "North". Otherwise the bonus is 0. Fill D2 and D3.
Questions
How do I write an IF with multiple conditions?
Put AND or OR inside the test: =IF(AND(A2>0, B2>0), "Both", "No") or =IF(OR(A2="X", A2="Y"), 1, 0).
How many IFs can be nested?
64 in current Excel, but anything past three is hard to read. Use IFS, or a lookup table with XLOOKUP for brackets.
How do I make IF return blank?
Return an empty string: =IF(A2="", "", A2*2). The cell looks empty but is not truly blank, so ISBLANK on it returns FALSE.
Related
- IFSHow the Excel IFS function checks several conditions in order and returns the first match, with grade and shipping tier examples and the fix for #N/A.
- AND, OR, NOTHow the Excel AND, OR and NOT functions test several conditions and return TRUE or FALSE, with examples inside IF, a between test and a not blank test.
- IFERRORHow the Excel IFERROR function returns a value you choose instead of an error, with examples for division by zero and VLOOKUP, and when IFNA is better.
- ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.