The IF function makes decisions in Excel. Test conditions and return different values based on whether the condition is TRUE or FALSE. Essential for automated calculations and logic.

Basic IF Syntax

Simple IF Statement

=IF(logical_test, value_if_true, value_if_false)

Example:
=IF(A2 > 100, "High", "Low")

If A2 is greater than 100, show "High"
Otherwise, show "Low"

Common Comparison Operators

=      Equal to                =IF(A2 = "Active", "Yes", "No")
>      Greater than            =IF(A2 > 1000, "Bonus", "")
<      Less than               =IF(A2 < 18, "Minor", "Adult")
>=     Greater than or equal   =IF(A2 >= 65, "Retire", "Work")
<=     Less than or equal      =IF(A2 <= 0, "Error", A2)
<>     Not equal to            =IF(A2 <> "", A2, "Blank")

Practical Business Examples

Pass/Fail Grading

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

B2 = test score
Pass if 60 or above, otherwise Fail

Sales Commission Calculator

=IF(A2 > 10000, A2 * 0.15, A2 * 0.10)

If sales > $10,000: 15% commission
Otherwise: 10% commission

Discount Pricing

=IF(B2 >= 100, A2 * 0.9, A2)

If quantity >= 100: 10% discount (multiply by 0.9)
Otherwise: regular price (no discount)

Status Based on Date

=IF(C2 < TODAY(), "Overdue", "On Time")

If due date is before today: Overdue
Otherwise: On Time

Nested IF Statements

Multiple Conditions (Grading Scale)

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

90+: A
80-89: B
70-79: C
60-69: D
Below 60: F

Tiered Commission Structure

=IF(A2 >= 50000, A2 * 0.20,
    IF(A2 >= 25000, A2 * 0.15,
        IF(A2 >= 10000, A2 * 0.10, A2 * 0.05)))

$50K+: 20%
$25K-$49.9K: 15%
$10K-$24.9K: 10%
Under $10K: 5%

Better Alternative: IFS Function (Excel 365)

=IFS(
    A2 >= 90, "A",
    A2 >= 80, "B",
    A2 >= 70, "C",
    A2 >= 60, "D",
    TRUE, "F"
)

Easier to read than nested IFs
Tests conditions in order until one is TRUE

IF with AND/OR Logic

IF with AND (All Conditions Must Be TRUE)

=IF(AND(B2 >= 18, C2 = "Active"), "Eligible", "Not Eligible")

Both conditions must be TRUE:
- Age >= 18 AND
- Status = "Active"

Use for: Qualification checks, multi-criteria approvals

IF with OR (Any Condition Can Be TRUE)

=IF(OR(B2 = "VIP", C2 > 50000), "Priority", "Standard")

Either condition TRUE:
- Customer is VIP OR
- Lifetime value > $50,000

Use for: Alternative qualification paths

Complex Logic Combinations

=IF(AND(B2 >= 60, OR(C2 = "Active", D2 = "Pending")), "Approve", "Reject")

Score >= 60 AND (Status is Active OR Pending)
All AND conditions + at least one OR condition must be TRUE

Advanced IF Techniques

IF with VLOOKUP

=IF(ISNA(VLOOKUP(A2, PriceTable, 2, FALSE)), "Not Found", VLOOKUP(A2, PriceTable, 2, FALSE))

Returns price if found, "Not Found" if not
Prevents #N/A errors from displaying

IF with ISNUMBER/ISTEXT

=IF(ISNUMBER(A2), A2 * 1.1, "Invalid Entry")

If A2 is a number: increase by 10%
Otherwise: show error message

Other useful tests: ISBLANK, ISERROR, ISTEXT

Blank Cell Handling

=IF(A2 = "", "Missing", A2)

If cell is blank: show "Missing"
Otherwise: show cell value

Or check for both blank and spaces:
=IF(TRIM(A2) = "", "Missing", A2)

Return Blank Instead of Zero

=IF(A2 = 0, "", A2)

If zero: return empty string (blank cell)
Otherwise: show value

Cleaner looking reports without zeros everywhere

Real-World Dashboard Examples

Traffic Light Status Indicator

=IF(B2 >= 100, "🟢 On Track",
    IF(B2 >= 70, "🟡 At Risk", "🔴 Critical"))

100%+: Green - On Track
70-99%: Yellow - At Risk
Below 70%: Red - Critical

Age Category Bucketing

=IF(A2 < 18, "Minor",
    IF(A2 < 35, "Young Adult",
        IF(A2 < 55, "Middle Age", "Senior")))

Perfect for demographic analysis

Inventory Reorder Alert

=IF(C2 <= D2, "REORDER NOW", IF(C2 <= D2*1.5, "Low Stock", "OK"))

C2 = Current stock
D2 = Reorder point

At or below reorder point: REORDER NOW
Within 50% above reorder point: Low Stock
Otherwise: OK

IF with Calculations

Overtime Pay Calculation

=IF(B2 > 40, (B2 - 40) * C2 * 1.5 + 40 * C2, B2 * C2)

B2 = Hours worked
C2 = Hourly rate

If hours > 40:
  Regular pay (40 hrs) + Overtime pay (1.5x rate)
Otherwise:
  Regular pay only

Conditional Markup

=IF(A2 = "Wholesale", B2 * 1.30, B2 * 1.50)

Wholesale customers: 30% markup
Retail customers: 50% markup

Text Manipulation with IF

Add Prefix Based on Condition

=IF(B2 = "Urgent", "⚠️ "&A2, A2)

If status is Urgent: add warning emoji
Otherwise: show name as-is

Conditional Concatenation

=IF(C2 <> "", A2 & " " & B2 & " " & C2, A2 & " " & B2)

If middle name exists: include it
Otherwise: just first and last name

Common Errors & Solutions

Error	Cause & Solution
#VALUE!	Comparing text to number. Use VALUE() or ensure consistent data types
#NAME?	Forgot quotes around text: Use "Yes" not Yes
Wrong result	Check operator (> vs >=) and order of nested IFs
All FALSE	Extra spaces in text. Use TRIM() or exact match

Performance Best Practices

• ✅ Limit nested IFs to 3-4 levels max (use IFS or SWITCH instead)
• ✅ Put most common condition first in nested IFs (faster)
• ✅ Use comparison operators correctly (>= vs >)
• ✅ Avoid volatile functions in IF when possible (NOW, TODAY, RAND)
• ❌ Don't nest IFs more than 7 levels (Excel's old limit)

Modern Alternatives (Excel 365)

IFS Function

=IFS(condition1, result1, condition2, result2, TRUE, default)

Cleaner than nested IFs
No need for multiple closing parentheses

SWITCH Function

=SWITCH(A2, "Red", 1, "Blue", 2, "Green", 3, "Other")

Better for exact match scenarios
Replaces multiple IF(A2="value") statements