Excel IF: Syntax and Examples
• The IF function evaluates a logical test and returns different values based on whether the condition is TRUE or FALSE, making it Excel's fundamental decision-making tool
Key Insights
• The IF function evaluates a logical test and returns different values based on whether the condition is TRUE or FALSE, making it Excel’s fundamental decision-making tool • Nested IF statements can handle multiple conditions but become difficult to maintain beyond 3-4 levels—consider using IFS or SWITCH functions for complex logic with many branches • Combining IF with AND/OR operators enables multi-criteria evaluation, allowing you to test multiple conditions simultaneously in a single formula
Introduction to the IF Function
The IF function is Excel’s workhorse for conditional logic. Every time you need a spreadsheet to make a decision—whether to flag an overdue invoice, calculate a tiered commission, or categorize data based on thresholds—you’re using IF logic. Master this function, and you unlock the ability to automate decisions that would otherwise require manual review of every row.
At its core, IF evaluates a condition and branches your formula down one of two paths. This binary decision-making forms the foundation for everything from simple pass/fail indicators to complex nested logic handling dozens of scenarios. Understanding IF isn’t just about memorizing syntax; it’s about thinking algorithmically within your spreadsheets.
Basic Syntax and Structure
The IF function requires three arguments in this exact order:
=IF(logical_test, value_if_true, value_if_false)
logical_test: Any expression that evaluates to TRUE or FALSE. This can be a comparison (A1>100), a function that returns a boolean (ISBLANK(A1)), or even a cell reference containing TRUE/FALSE.
value_if_true: What Excel returns when the logical test evaluates to TRUE. This can be a number, text string, cell reference, or even another formula.
value_if_false: What Excel returns when the logical test evaluates to FALSE. Same flexibility as value_if_true.
Excel evaluates the logical test first. If TRUE, it immediately returns value_if_true and ignores value_if_false entirely. If FALSE, it skips value_if_true and returns value_if_false. This evaluation order matters when you’re using formulas with side effects or performance implications.
Here’s a simple example checking if a sales figure exceeds a quota:
=IF(B2>100, "Quota Met", "Below Quota")
If cell B2 contains 150, Excel evaluates 150>100 (TRUE) and returns “Quota Met”. If B2 contains 75, the test is FALSE, and it returns “Below Quota”.
Simple IF Function Examples
Let’s look at practical single-condition scenarios you’ll encounter constantly.
Pass/Fail Grading:
=IF(C2>=70, "Pass", "Fail")
This checks if a student’s score in C2 meets the 70-point threshold. Clean, readable, and instantly understandable.
Discount Eligibility:
=IF(D2>=500, D2*0.1, 0)
Customers spending $500 or more get a 10% discount. Notice the value_if_false is 0 (no discount) rather than text. The result is numeric, which you can sum or use in further calculations.
Status Indicators Based on Dates:
=IF(E2<TODAY(), "Overdue", "Current")
This compares a due date in E2 against today’s date. Any date in the past triggers “Overdue”. This is invaluable for task management, invoice tracking, or subscription monitoring.
Binary Flag for Further Filtering:
=IF(F2="Premium", 1, 0)
Converting text categories to 1/0 flags makes filtering and summing easier. You can then use SUMIF to count premium customers or filter pivot tables more efficiently.
Nested IF Statements
When you need to evaluate multiple conditions in sequence, you nest IF functions by placing another IF as the value_if_false argument. Each nested level adds another branch to your decision tree.
Letter Grade Assignment:
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
This evaluates scores from highest to lowest. If A2 is 85, Excel checks: Is it ≥90? No. Is it ≥80? Yes—return “B” and stop. The remaining nested IFs never execute.
Shipping Cost by Weight Tiers:
=IF(B2<=1, 5, IF(B2<=5, 10, IF(B2<=10, 15, 20)))
This implements a tiered pricing structure: $5 for 1 lb or less, $10 for 1-5 lbs, $15 for 5-10 lbs, and $20 for anything heavier. Notice how each condition builds on the previous one—we don’t need to specify B2>1 in the second IF because we only reach it when B2>1 is already true.
Performance Rating System:
=IF(C2>=95, "Exceptional", IF(C2>=85, "Exceeds Expectations", IF(C2>=70, "Meets Expectations", IF(C2>=50, "Needs Improvement", "Unsatisfactory"))))
Five-tier rating systems are common in HR and performance management. This formula handles all five categories, but it’s already pushing the limits of readability.
IF with Logical Operators (AND, OR, NOT)
Single conditions are limiting. Real-world decisions often require multiple criteria evaluated together. That’s where AND, OR, and NOT operators transform IF from simple to powerful.
Bonus Eligibility with AND:
=IF(AND(D2>=100000, E2>=2), D2*0.05, 0)
Employees get a 5% bonus only if they hit $100K in sales AND have at least 2 years tenure. Both conditions must be TRUE. If either fails, the bonus is 0.
Alert System with OR:
=IF(OR(F2<10, G2>90), "WARNING", "Normal")
This triggers a warning if inventory drops below 10 units OR temperature exceeds 90 degrees. Either condition being TRUE is sufficient.
Multi-Criteria Qualification:
=IF(AND(H2="Active", I2>=1000, J2<30), "Qualified", "Not Qualified")
A customer qualifies only if their status is Active AND they’ve spent at least $1000 AND they have fewer than 30 days since last purchase. All three conditions must align.
Exclusion Logic with NOT:
=IF(NOT(K2="Cancelled"), L2*1.1, L2)
Apply a 10% increase to all orders except cancelled ones. NOT inverts the boolean—if K2 is “Cancelled”, NOT makes it FALSE, and we skip the increase.
Complex Combinations:
=IF(AND(M2="Premium", OR(N2>5000, O2>=3)), "Platinum Upgrade", "Standard")
Premium customers get upgraded to Platinum if they’ve either spent over $5000 OR have been members for 3+ years. This combines AND and OR for sophisticated multi-factor logic.
Common Pitfalls and Best Practices
Nesting Limitations: Excel supports up to 64 nested IF levels, but you’ll lose your mind long before hitting that limit. Beyond 3-4 levels, readability collapses. Consider using the IFS function instead:
# Deeply nested IF (hard to read)
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))
# IFS function (cleaner)
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")
IFS evaluates condition/value pairs in sequence and returns the first match. The final TRUE acts as a catch-all default.
Text Comparison Case Sensitivity: IF treats “active” and “Active” as different. Use UPPER() or LOWER() to normalize:
=IF(UPPER(B2)="ACTIVE", "Yes", "No")
Blank Cell Handling: Excel treats empty cells as 0 in numeric comparisons, which can produce unexpected results:
=IF(C2>0, "Positive", "Zero or Negative")
If C2 is blank, this returns “Zero or Negative” because blank equals 0. If you need to distinguish blanks:
=IF(C2="", "No Data", IF(C2>0, "Positive", "Zero or Negative"))
Order Matters in Nested IFs: Always test from most restrictive to least restrictive. Testing A2>=60 before A2>=90 would assign “D” to a 95-point score.
Alternative Functions: For multiple conditions returning different values, SWITCH is often cleaner than nested IFs:
=SWITCH(D2, "Small",5, "Medium",10, "Large",15, "X-Large",20, 0)
Real-World Applications
Invoice Payment Status Tracker:
=IF(E2="", "Not Paid", IF(E2<=F2, "Paid On Time", "Paid Late"))
Column E contains payment date, F contains due date. If E is blank, invoice is unpaid. If payment date is on or before due date, it’s on time. Otherwise, it’s late.
Inventory Reorder Alert System:
=IF(G2<=H2, "REORDER NOW - Stock at " & G2, IF(G2<=H2*1.5, "Low Stock Warning", "Adequate"))
G2 is current stock, H2 is reorder point. This creates three alert levels: critical (at or below reorder point), warning (within 50% above reorder point), and adequate. The concatenation provides specific stock levels for critical items.
Commission Calculator with Tiered Rates:
=IF(I2<10000, I2*0.02, IF(I2<25000, I2*0.03, IF(I2<50000, I2*0.04, I2*0.05)))
Sales under $10K earn 2%, $10K-$25K earn 3%, $25K-$50K earn 4%, and $50K+ earn 5%. This tiered structure incentivizes higher performance without requiring complex lookup tables.
Dynamic Deadline Extension:
=IF(AND(J2="High Priority", K2-TODAY()<=3), K2+7, K2)
High-priority projects within 3 days of deadline automatically get a 7-day extension. This combines date arithmetic with conditional logic to implement business rules directly in formulas.
The IF function’s simplicity is deceptive. While the syntax is straightforward, combining IF with other functions and operators creates sophisticated decision-making logic that automates complex business rules. Start with simple conditions, test thoroughly, and graduate to nested and compound logic as your confidence grows. When formulas become unreadable, that’s your signal to refactor using IFS, SWITCH, or helper columns that break complex logic into manageable steps.