IF

How IF works

IF asks one yes-or-no question and reacts. If the answer is yes, it returns the second argument. If the answer is no, it returns the third. That's the whole function.

The first argument is a comparison: is this number bigger than that one, does this cell equal that text, is this date in the past. Excel evaluates it to TRUE or FALSE, then picks the matching branch.

IF is the building block for almost every business rule in Excel. Every flag, every threshold, every conditional calculation eventually traces back to one or more IFs.

= IF(logical_test, value_if_true, [value_if_false])

Arguments

IF basic: turn a quiz score into Pass or Fail

You're a teaching assistant grading a midterm. Anything 60 or above is a pass, anything below is a fail. You have 200 students. One formula handles all of them.

=IF(B2>=60,"Pass","Fail")

• •B2>=60 → the question being asked: is the score 60 or higher?
• •"Pass" → what to write when the answer is yes
• •"Fail" → what to write when the answer is no
• •B2 → the cell being checked (drag down and it becomes B3, B4, etc.)

IF on dates: flag invoices that are overdue

You manage receivables. The aging report has a column for days outstanding. Anything over 30 days needs a phone call today. You want a column that says "Overdue" or "Current" so you can filter and call.

=IF(B2>30,"Overdue","Current")

• •B2>30 → is days outstanding strictly greater than 30?
• •"Overdue" → label when yes
• •"Current" → label when no (30 days or less)
• •B2 → the days-outstanding cell on the row being checked

Nested IF: convert scores to letter grades

One IF asks one question. To handle multiple bands you nest them: each "value if false" branch holds another IF that asks the next question. Letter grades are the textbook example: 90 and above is A, 80s are B, 70s are C, 60s are D, anything else is F.

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

• •First test → is the score 90 or higher? If yes, return A and stop.
• •Second test → if not, is it 80 or higher? Return B.
• •Third test → if not, is it 70 or higher? Return C.
• •Fourth test → if not, is it 60 or higher? Return D.
• •Final fallback → if every test failed, return F.
• •Order matters → Excel reads top to bottom and stops at the first match. Test the highest band first.

IF in a calculation: commission at two tiers

The branches don't have to be text. They can be cell references, formulas, anything Excel can evaluate. A classic example: salespeople earn 10% commission on anything at or above $10,000 in monthly revenue, and 5% otherwise. One IF, doing math.

=IF(B2>=10000,B2*0.1,B2*0.05)

• •B2>=10000 → did this rep clear the $10K bar?
• •B2*0.1 → 10% of revenue if yes
• •B2*0.05 → 5% of revenue if no
• •B2 → the rep's monthly revenue cell

IF with AND: bonus eligibility on two conditions

Real business rules rarely depend on one number. Bonus eligibility might require both tenure of three years or more AND a performance rating of 4 or higher. Wrap both conditions in AND() and feed that into IF as the test.

=IF(AND(B2>=3,C2>=4),"Eligible","Not yet")

• •AND(B2>=3,C2>=4) → both must be true: at least 3 years AND rating 4 or higher
• •B2 → the years-of-service cell
• •C2 → the latest performance rating
• •"Eligible" / "Not yet" → the two outcomes
• •Swap AND for OR → if either condition is enough on its own

Where people go wrong

1. 01
   Wrapping numbers in quotes

   Writing =IF(B2>"60","Pass","Fail") looks fine but the quotes turn 60 into text. Excel then compares text to text, which gives wrong results because "9" is greater than "60" in alphabetical order.

   Fix: Drop the quotes around numeric thresholds. Quotes belong around words, not numbers.
2. 02
   Forgetting the false branch

   Writing =IF(B2>=60,"Pass") and skipping the third argument means Excel returns the literal value FALSE for any score under 60. Your column ends up with a mix of "Pass" and FALSE, which breaks filters and looks broken.

   Fix: Always supply the third argument, even if it is just "" for blank.
3. 03
   Stacking too many nested IFs

   By the fifth nested IF the formula becomes unreadable and one stray parenthesis breaks everything. You'll lose half a morning hunting the bug.

   Fix: When you reach four bands, switch to IFS, SWITCH, or a small bracket table you VLOOKUP against.
4. 04
   Comparing numbers stored as text

   Numbers imported from a website or pasted from a PDF often arrive as text. =IF(B2>30, ...) silently misbehaves because text doesn't compare numerically.

   Fix: Wrap the cell in VALUE() to force a number, or fix the column type once at the source. Watch for left-aligned 'numbers' in a column that should be right-aligned.

Notes

• IF is case-insensitive when comparing text. "apple" equals "APPLE"
• The third argument (value_if_false) is technically optional. If you skip it, Excel returns the literal value FALSE, which is almost never what you want
• IF can be nested up to 64 levels deep, but anything past three is hard to maintain and worth refactoring
• Both branches can be formulas, cell references, or other functions. Not just static text or numbers
• Comparison operators: = equals, <> not equal, > greater than, < less than, >= at least, <= at most
• For multiple AND conditions wrap them in AND(). For any-of conditions wrap them in OR(). NOT() inverts a test
• In Excel 2019 and later, IFS handles multi-band logic much more cleanly than nested IFs

What to learn next

Now prove it