Excel IF Function – Complete Guide (2025)

Basic Use of IF Function

The IF function checks a condition and returns different results for TRUE or FALSE. Here are common examples:

Pass or Fail in Exams 🎓

• Goal: Decide if a student passes based on marks in A2.
• Inputs: A2 is the score (0–100).
• Condition checked: Is A2 ≥ 40?
  → If true: Pass
  → If false: Fail
• Why it works: Simple threshold converts marks into performance status.
• Tip: Use absolute references $A$2 if copying formula.
• Common mistake: Comparing text "40" instead of number; keep A2 numeric.
• Variation: Use emojis: "✅" / "❌".

Discount Eligibility 🎁

• Goal: Flag orders that qualify for a promotion.
• Inputs: B2 is order amount (₹).
• Condition checked: Is B2 ≥ 500?
  → If true: Discount
  → If false: No Discount
• Why it works: Clear price thresholds reduce ambiguity.
• Tip: Use conditional formatting to highlight qualifying rows.
• Common mistake: Currency symbols inside numbers; keep B2 numeric.
• Variation: Return actual discount: =IF(B2>=500,B2*0.1,0).

Age Check 👤

• Goal: Categorize users for compliance or targeting.
• Inputs: C2 is age in years.
• Condition checked: Is C2 ≥ 18?
  → If true: Adult
  → If false: Minor
• Why it works: Clear cut-offs simplify eligibility rules.
• Tip: Validate C2 isn’t empty or negative.
• Common mistake: Age stored as text; use VALUE() if needed.
• Variation: Add “Senior” using nested IF: =IF(C2>=60,"Senior",IF(C2>=18,"Adult","Minor")).

Attendance Warning 📊

• Goal: Flag low attendance for intervention.
• Inputs: D2 is attendance percentage.
• Condition checked: Is D2 < 75?
  → If true: Warning
  → If false: Good
• Why it works: Immediate color-coded status helps educators act early.
• Tip: Protect threshold in a cell (e.g., $H$1) and use =IF(D2<$H$1,"Warning","Good").
• Common mistake: Percent formats vs decimals; 75% = 0.75, not 75.
• Variation: Return guidance text: “Meet mentor” / “Keep it up”.

Free Delivery 🚚

• Goal: Apply delivery fees based on cart value.
• Inputs: E2 is order total.
• Condition checked: Is E2 ≥ 1000?
  → If true: Free
  → If false: ₹50 Charge
• Why it works: Transparent rules reduce checkout friction.
• Tip: Store fee in a control cell (e.g., $H$2) to update policies easily.
• Common mistake: Forgetting taxes; ensure E2 represents subtotal correctly.
• Variation: Return numeric fee: =IF(E2>=1000,0,50).

Bonus Eligibility 🎁

• Goal: Identify employees qualifying for long-service bonuses.
• Inputs: F2 is years of service.
• Condition checked: Is F2 ≥ 10?
  → If true: Bonus
  → If false: No Bonus
• Why it works: Keeps recognition consistent and fair.
• Tip: If F2 is a joining date, compute years first: =DATEDIF(F2,TODAY(),"y").
• Common mistake: Using month differences; stick to DATEDIF for years.
• Variation: Tiered bonuses with nested IF or IFS.

Temperature Alert 🌡️

• Goal: Flag high temperatures for safety or quality control.
• Inputs: G2 is temperature in °C.
• Condition checked: Is G2 > 35?
  → If true: Hot
  → If false: Normal
• Why it works: Single threshold keeps dashboards clear.
• Tip: Use ROUND(G2,1) if sensor noise causes flickering.
• Common mistake: Mixed units; ensure all readings are °C.
• Variation: Add "Cold" band: =IF(G2<15,"Cold",IF(G2>35,"Hot","Normal")).

Stock Status 📦

• Goal: Show inventory availability at a glance.
• Inputs: H2 is quantity on hand.
• Condition checked: Is H2 ≤ 0?
  → If true: Out of Stock
  → If false: Available
• Why it works: Minimizes overselling and customer disappointment.
• Tip: Include safety stock logic: =IF(H2<=SafetyStock,"Reorder","OK").
• Common mistake: Negative stock due to returns; reconcile before reporting.
• Variation: Return reorder quantity with a formula instead of text.

Late Submission ⏰

• Goal: Flag tardiness for assignments or tasks.
• Inputs: I2 is days delayed.
• Condition checked: Is I2 > 0?
  → If true: Late
  → If false: On Time
• Why it works: Simple binary feedback encourages punctuality.
• Tip: Auto-calc delay: =MAX(0,ActualDate-DueDate) to avoid negatives.
• Common mistake: Comparing dates as text; store proper date values.
• Variation: Add severity tiers: "1–2 days: Gentle reminder", "3+ days: Escalate".

Grade Category (Nested IF) 🏆

• Goal: Assign grade bands from numeric scores.
• Inputs: J2 is the numeric score.
• Condition sequence:
  • First check: Is J2 ≥ 90? → A
  • Else second check: Is J2 ≥ 75? → B
  • Else: C
• Why it works: Ordered checks create clean bands without overlap.
• Tip: Keep bands in a reference table and use LOOKUP for scalability.
• Common mistake: Unsorted or overlapping thresholds; define ranges clearly.
• Variation: Use IFS for readability (Excel 2016+): =IFS(J2>=90,"A",J2>=75,"B",TRUE,"C")

Intermediate Use

We will add Nested IF & AND/OR integration examples here.

Salary Tax Bracket 💰

• Goal: Categorize employees into tax brackets.
• Inputs: A2 is annual salary.
• Condition:
  • If A2 > 50,000 → High Tax
  • Else → Low Tax
• Why it works: Simple threshold separates two categories clearly.
• Tip: Store tax limits in a reference cell for easy updates.
• Common mistake: Forgetting currency formatting; keep A2 numeric.
• Variation: Use nested IF for multiple brackets.

Loan Approval (AND) 🏦

• Goal: Approve loans based on credit score and income.
• Inputs: B2 = credit score, C2 = income.
• Condition: Both conditions must be true: score ≥700 AND income ≥30,000.
• Why it works: Combines multiple eligibility rules.
• Tip: Use AND for strict conditions, OR for flexible ones.
• Common mistake: Mixing text and numbers in credit score.
• Variation: Add nested IF for “Pending Review.”

Attendance + Grade (OR) 📊

• Goal: Check eligibility based on attendance or marks.
• Inputs: D2 = attendance %, E2 = marks.
• Condition: If attendance <75% OR marks <40 → Not Eligible; else Eligible.
• Why it works: OR ensures either condition can disqualify.
• Tip: Use OR when any one condition is enough.
• Common mistake: Confusing OR with AND.
• Variation: Add nested IF for “Conditional Eligibility.”

Expiry Check ⏳

• Goal: Flag expired products or memberships.
• Inputs: F2 = expiry date.
• Condition: If F2 < TODAY() → Expired; else Valid.
• Why it works: TODAY() auto-updates daily.
• Tip: Format F2 as date, not text.
• Common mistake: Comparing text dates instead of real dates.
• Variation: Show days left: =IF(F2<TODAY(),"Expired",F2-TODAY()).

Bonus Tier (Nested IF) 🏅

• Goal: Assign tiered bonuses by years of service.
• Inputs: G2 = years of service.
• Condition sequence:
  • ≥20 → Gold
  • ≥10 → Silver
  • Else → Bronze
• Why it works: Ordered checks create clear tiers.
• Tip: Use LOOKUP for scalability.
• Common mistake: Overlapping ranges.
• Variation: Use IFS for cleaner syntax.

Empty Cell Check ⚠️

• Goal: Detect missing data.
• Inputs: H2 = cell value.
• Condition sequence:
  • If blank → Missing
  • Else → OK
• Why it works: ISBLANK ensures data completeness.
• Tip: Use with conditional formatting for alerts.
• Common mistake: Spaces count as non-blank.

Late Fee Calculation ⏰

• Goal: Calculate late fees.
• Inputs: I2 = days late.
• Condition sequence:
  • If >0 → fee = days × ₹50
  • Else → 0
• Why it works: Combines IF with arithmetic.
• Tip: Use MAX(0,…) to avoid negatives.
• Common mistake: Forgetting to multiply by fee rate.
• Variation: Use tiered fees with nested IF.

Profit/Loss 📈📉

• Goal: Show business outcome.
• Inputs: J2 = revenue, K2 = cost.
• Condition sequence:
  • If revenue – cost >0 → Profit
  • Else → Loss
• Why it works: Simple calculation for clarity.
• Tip: Return actual profit/loss value instead of text.
• Common mistake: Mixing revenue and cost columns.
• Variation: Add Break-even case with nested IF.

Grade with Attendance (AND) 🎓

• Goal: Assign grade considering both score and attendance.
• Inputs: L2 = score, M2 = attendance %.
• Condition sequence:
  • If score ≥90 AND attendance ≥80% → A
  • Else → B
• Why it works: Ensures holistic evaluation.
• Tip: Adjust thresholds in reference cells.
• Common mistake: Using OR instead of AND.
• Variation: Add more grade bands with nested IF.

Weekend Check 📅

• Goal: Identify weekends from dates.
• Inputs: N2 = date.
• Condition sequence:
  • If weekday = 1 (Sunday) OR 7 (Saturday) → Weekend
  • Else → Weekday
• Why it works: WEEKDAY returns day numbers.
• Tip: Adjust WEEKDAY’s return type for regional calendars.
• Common mistake: Misinterpreting weekday numbering.
• Variation: Use CHOOSE with WEEKDAY for custom labels.

Advanced Use

Conditional Bonus with SUMIF 💰

• Goal: Reward a team if at least 5 sales exceed ₹1000.
• Inputs: B2:B10 = sales values.
• Condition sequence:
  • SUMIF counts qualifying sales.
  • If ≥5 → Team Bonus
  • Else → No Bonus
• Why it works: Combines IF with SUMIF for group logic.
• Tip: Use COUNTIF if only counts needed.
• Variation: Scale bonus by number of qualifying sales.

Attendance + Grade with COUNTIF 📊

• Goal: Flag students with more than 3 low-attendance records.
• Inputs: D2:D30 = attendance %.
• Condition sequence:
  • COUNTIF counts <75%
  • If >3 → Review
  • Else → Clear
• Why it works: Automates checks across ranges.
• Tip: Use conditional formatting for visual alerts.
• Variation: Replace threshold with reference cell.

Lookup with IF + INDEX/MATCH 🔍

• Goal: Return product price if product code exists.
• Inputs: E2 = product code, G2:G10 = codes, F2:F10 = prices.
• Condition sequence:
  • If E2 blank → Missing
  • Else → lookup price
• Why it works: Combines IF with INDEX/MATCH for flexible lookups.
• Tip: Use MATCH with 0 for exact matches.
• Variation: Replace with XLOOKUP in newer Excel versions.

Tiered Commission (IFS) 💹

• Goal: Assign commission rates by sales tiers.
• Inputs: H2 = sales amount.
• Condition sequence:
  • ≥100,000 → 20%
  • ≥50,000 → 10%
  • ≥20,000 → 5%
  • Else → 0%
• Why it works: IFS simplifies nested IF chains.
• Tip: Order conditions from highest to lowest.
• Variation: Use LOOKUP for scalability.

Dynamic Due Date Check 📅

• Goal: Track task deadlines.
• Inputs: I2 = due date.
• Condition sequence:
  • If today > due date → Overdue
  • If today = due date → Due Today
  • Else → On Track
• Why it works: Nested IF handles multiple time states.
• Tip: Use conditional formatting for urgency colors.
• Variation: Add days remaining with I2-TODAY().

Multi-Criteria Eligibility (AND + OR) ✅

• Goal: Check if candidate meets score and certification criteria.
• Inputs: J2 = score, K2 = training, L2 = certification.
• Condition sequence:
  • Score ≥80 AND (training = Yes OR certified)
• Why it works: Combines AND + OR for complex rules.
• Tip: Break logic into helper columns for clarity.
• Variation: Use IFS for multiple eligibility paths.

Profit Margin Category 📈

• Goal: Classify products by profit margin.
• Inputs: M2 = revenue, N2 = cost.
• Condition sequence:
  • Margin ≥30% → High Margin
  • Else → Low Margin
• Why it works: IF evaluates calculated ratios.
• Tip: Format as percentage for clarity.
• Variation: Add “Medium Margin” tier with nested IF.

Conditional Formatting Helper 🚦

• Goal: Assign traffic-light labels for performance.
• Inputs: O2 = score.
• Condition sequence:
  • O2 <50 → Red
  • O2 <75 → Yellow
  • Else → Green
• Why it works: Supports visual dashboards.
• Tip: Link to conditional formatting for colors.
• Variation: Use IFS for cleaner syntax.

Inventory Reorder Logic 📦

• Goal: Trigger reorder when stock ≤ safety level.
• Inputs: P2 = current stock, Q2 = safety stock.
• Condition sequence:
  • Stock ≤ safety → Reorder
  • Else → Sufficient
• Why it works: Simple threshold check for inventory control.
• Tip: Keep safety stock in a reference table.
• Variation: Add reorder quantity calculation.

Scholarship Eligibility 🎓

• Goal: Award scholarships based on marks and extracurriculars.
• Inputs: R2 = marks, S2 = extracurricular participation.
• Condition sequence:
  • Marks ≥85 AND extracurricular = Yes → Eligible
  • Else → Not Eligible
• Why it works: Combines academic and activity criteria.
• Tip: Use OR if either condition is enough.
• Variation: Add tiers like “Partial Scholarship.”