1
Core Concept & 3-Part Formula Anatomy
The IF function is the foundation of programmatic logic in Excel. It evaluates a specific condition (the logical test) and returns one result if the test is TRUE, and another result if the test is FALSE.
=IF(logical_test, value_if_true, [value_if_false])
Visual Decision Branching Tree
Logical Test
e.g. Sales >= Target
e.g. Sales >= Target
──▶
If TRUE (Branch 1)
"Target Met" or Sales * 0.10
"Target Met" or Sales * 0.10
──OR──▶
If FALSE (Branch 2)
"Below Target" or 0
"Below Target" or 0
2
Logical Comparison Operators in Excel
Excel uses 6 standard comparison operators to evaluate values in a logical test:
| Operator | Meaning | Example Formula | Evaluates To |
|---|---|---|---|
= | Equal to | =IF(A2 = "Delhi", "Local", "Outstation") | TRUE if cell A2 is "Delhi" |
> | Strictly greater than | =IF(B2 > 100, "High", "Low") | TRUE if B2 is 101+ |
>= | Greater than or equal to | =IF(B2 >= 50, "Pass", "Fail") | TRUE if B2 is 50, 51, 52... |
< | Strictly less than | =IF(C2 < 10, "Reorder", "OK") | TRUE if inventory is 9 or less |
<= | Less than or equal to | =IF(D2 <= 0, "Out of Stock", "Available") | TRUE if D2 is 0 or negative |
<> | Not equal to | =IF(E2 <> 0, "Active", "Inactive") | TRUE if E2 is anything other than 0 |
3
Text vs. Numeric Return Values
1. Returning Text Labels (Use Quotes):
=IF(B2>=C2, "Target Met", "Below Target")
Always wrap text strings in double quotes. Omitting quotes causes a #NAME? error.
2. Returning Numbers / Calculations (NO Quotes):
=IF(B2>=50000, B2*0.10, 0)
Never put quotes around numbers (e.g. "0"), otherwise Excel treats them as strings and breaks downstream math!
5
Syntax Errors vs. Logic Errors
Crucial Concept for Analysts
- Syntax Error (Crash): Missing parentheses, unclosed quotes (e.g.
=IF(B2>=C2, "Met", "Missed"). Excel will refuse to accept the formula. - Logic Error (Silent Disaster): Inverting comparison operators (e.g.
=IF(B2 < C2, "Target Met", "Below Target")). The formula runs perfectly with 0 warnings, but pays bonuses to underperforming employees!
⚡ Live Interactive Excel Worksheet — IF Function Lab
Experience authentic Excel conditional logic: write formulas, fill down across rows, compute numeric discounts, and debug logic mistakes
Task: Identify whether each sales employee met their monthly target.
Rule: If Sales (B2) >= Target (C2) return "Target Met", otherwise return "Below Target".
fx
| # | A (Employee) | B (Sales ₹) | C (Target ₹) | D (Status) |
|---|---|---|---|---|
| 2 | Amit Sharma | ₹75,000 | ₹60,000 | =IF(...) |
| 3 | Priya Patel | ₹45,000 | ₹60,000 | =IF(...) |
| 4 | Rahul Verma | ₹90,000 | ₹60,000 | =IF(...) |
| 5 | Neha Singh | ₹55,000 | ₹60,000 | =IF(...) |