IF Function in Excel Tutorial

Excel Tutorial

Introduction

The IF function in Excel is used to make decisions based on a condition. It checks whether a condition is TRUE or FALSE and returns one result when the condition is TRUE and another result when it is FALSE.

For example, an HR department can use the IF function to check whether an employee has passed a performance target. Similarly, teachers can use it to identify students who have passed or failed an examination.

In this tutorial, you will learn how to use the IF function in Excel with practical examples.

IF Function Syntax

=IF(logical_test, value_if_true, value_if_false)

Arguments:

  • logical_test: The condition you want Excel to check.
  • value_if_true: The result Excel returns when the condition is TRUE.
  • value_if_false: The result Excel returns when the condition is FALSE.

Example:

=IF(A2>=40,"Pass","Fail")

If A2 contains 65, Excel returns Pass. If A2 contains 35, Excel returns Fail.

Question 1: Check Student Pass or Fail Status

Problem Statement:

A teacher wants to check whether students have passed or failed an examination. Students who score 40 or more marks are considered passed.

Use the IF function to display Pass or Fail.

Practical Data:

Student NameMarks
Amit78
Priya35
Rahul65
Neha28
Karan40

Assume student marks are in cells B2.

Excel Formula:

Enter this formula in C2:

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

Copy the formula down to C6.

Expected Result:

Student NameMarksResult
Amit78Pass
Priya35Fail
Rahul65Pass
Neha28Fail
Karan40Pass

Explanation:

The IF function checks whether the marks are greater than or equal to 40. If the condition is TRUE, Excel displays Pass. Otherwise, it displays Fail.

Question 2: Check Employee Salary Eligibility

Problem Statement:

An HR department wants to identify employees whose basic salary is ₹30,000 or more. Employees meeting this condition are marked Eligible, while others are marked Not Eligible.

Practical Data:

Employee NameBasic Salary
Amit25000
Priya35000
Rahul42000
Neha28000
Karan30000

Assume basic salaries are stored in B2.

Excel Formula:

=IF(B2>=30000,"Eligible","Not Eligible")

Enter the formula in C2 and copy it down.

Expected Result:

Employee NameBasic SalaryEligibility
Amit25000Not Eligible
Priya35000Eligible
Rahul42000Eligible
Neha28000Not Eligible
Karan30000Eligible

Explanation:

The formula checks whether the employee’s basic salary is at least ₹30,000. If it is, the result is Eligible. Otherwise, it displays Not Eligible.

Question 3: Check Employee Attendance Status

Problem Statement:

A company considers an employee present if their attendance is 22 days or more in a month.

Use the IF function to display Present or Low Attendance.

Practical Data:

Employee NameAttendance Days
Amit25
Priya20
Rahul23
Neha18
Karan22

Assume attendance days are stored in B2.

Excel Formula:

=IF(B2>=22,"Present","Low Attendance")

Enter the formula in C2 and copy it down.

Expected Result:

Employee NameAttendance DaysAttendance Status
Amit25Present
Priya20Low Attendance
Rahul23Present
Neha18Low Attendance
Karan22Present

Explanation:

The formula checks whether attendance is 22 days or more. Employees meeting the condition receive the Present status.

Question 4: Check Sales Target Achievement

Problem Statement:

A company has assigned a monthly sales target of ₹100,000 to each salesperson.

If an employee achieves sales of ₹100,000 or more, display Target Achieved. Otherwise, display Target Not Achieved.

Practical Data:

SalespersonMonthly Sales
Amit120000
Priya85000
Rahul100000
Neha95000
Karan150000

Assume monthly sales are stored in B2.

Excel Formula:

=IF(B2>=100000,"Target Achieved","Target Not Achieved")

Enter the formula in C2 and copy it down.

Expected Result:

SalespersonMonthly SalesTarget Status
Amit120000Target Achieved
Priya85000Target Not Achieved
Rahul100000Target Achieved
Neha95000Target Not Achieved
Karan150000Target Achieved

Explanation:

The IF function compares each salesperson’s monthly sales with the target of ₹100,000. Sales equal to or above the target are considered achieved.

Question 5: Check Product Stock Availability

Problem Statement:

A store manager wants to check whether products are available in stock.

If the stock quantity is greater than zero, display In Stock. If the quantity is zero, display Out of Stock.

Practical Data:

Product NameStock Quantity
Keyboard25
Mouse0
Monitor12
Printer0
Laptop8

Assume stock quantities are stored in B2.

Excel Formula:

=IF(B2>0,"In Stock","Out of Stock")

Enter the formula in C2 and copy it down.

Expected Result:

Product NameStock QuantityStock Status
Keyboard25In Stock
Mouse0Out of Stock
Monitor12In Stock
Printer0Out of Stock
Laptop8In Stock

Explanation:

The formula checks whether the stock quantity is greater than zero. If products are available, Excel displays In Stock. Otherwise, it displays Out of Stock.

Question 6: Calculate Student Grades Using Nested IF

Problem Statement:

A school wants to assign grades to students based on their examination marks.

Use Nested IF to assign grades according to the following rules:

  • Marks 90 or above: Grade A
  • Marks 75 or above: Grade B
  • Marks 60 or above: Grade C
  • Marks 40 or above: Grade D
  • Marks below 40: Fail

Practical Data:

Student NameMarks
Amit95
Priya82
Rahul68
Neha45
Karan30

Excel Formula:

=IF(B2>=90,"Grade A",IF(B2>=75,"Grade B",IF(B2>=60,"Grade C",IF(B2>=40,"Grade D","Fail"))))

Expected Result:

Student NameMarksGrade
Amit95Grade A
Priya82Grade B
Rahul68Grade C
Neha45Grade D
Karan30Fail

Explanation:

This formula uses multiple IF conditions inside one another. Excel checks each condition in order. When a condition becomes TRUE, it returns the corresponding grade.

Question 7: Check Employee Bonus Eligibility Using AND

Problem Statement:

A company provides a performance bonus only when an employee meets both conditions:

  • Monthly sales must be ₹100,000 or more.
  • Performance score must be 80 or more.

Use IF with AND to check bonus eligibility.

Practical Data:

Employee NameMonthly SalesPerformance Score
Amit12000090
Priya8500085
Rahul11000075
Neha15000095
Karan10000080

Excel Formula:

=IF(AND(B2>=100000,C2>=80),"Bonus Eligible","Not Eligible")

Expected Result:

Employee NameMonthly SalesPerformance ScoreBonus Status
Amit12000090Bonus Eligible
Priya8500085Not Eligible
Rahul11000075Not Eligible
Neha15000095Bonus Eligible
Karan10000080Bonus Eligible

Explanation:

AND checks whether both conditions are TRUE. The IF function returns Bonus Eligible only when both requirements are satisfied.

Question 8: Check Employee Incentive Eligibility Using OR

Problem Statement:

A company offers a special incentive to employees who meet at least one of these conditions:

  • Monthly sales are ₹150,000 or more.
  • Performance score is 95 or more.

Use IF with OR to check incentive eligibility.

Practical Data:

Employee NameMonthly SalesPerformance Score
Amit16000085
Priya9000096
Rahul12000080
Neha15000090
Karan8000095

Excel Formula:

=IF(OR(B2>=150000,C2>=95),"Incentive Eligible","Not Eligible")

Expected Result:

Employee NameMonthly SalesPerformance ScoreIncentive Status
Amit16000085Incentive Eligible
Priya9000096Incentive Eligible
Rahul12000080Not Eligible
Neha15000090Incentive Eligible
Karan8000095Incentive Eligible

Explanation:

OR checks whether at least one condition is TRUE. If either sales or performance score meets the requirement, the employee becomes eligible.nction checks whether an employee has completed at least 6 months. If the condition is TRUE, the employee is marked Confirmed.

Question 9: Check Whether a Salesperson Has Exceeded the Target

Problem Statement:

A company has set a monthly sales target of ₹100,000. Management wants to identify salespeople who have exceeded the target, not just achieved it.

If sales are greater than ₹100,000, display Above Target. Otherwise, display Target Not Exceeded.

Practical Data:

SalespersonMonthly Sales
Amit125000
Priya100000
Rahul115000
Neha90000
Karan100000

Assume monthly sales are stored in B2.

Excel Formula:

=IF(B2>100000,"Above Target","Target Not Exceeded")

Enter the formula in C2 and copy it down.

Expected Result:

SalespersonMonthly SalesResult
Amit125000Above Target
Priya100000Target Not Exceeded
Rahul115000Above Target
Neha90000Target Not Exceeded
Karan100000Target Not Exceeded

Explanation:

The formula uses the greater-than (>) operator. Therefore, sales must be more than ₹100,000 to display Above Target. Sales equal to ₹100,000 do not meet this condition.

Question 10: Calculate Employee Incentive Based on Sales

Problem Statement:

A company provides a fixed incentive of ₹5,000 to sales employees who achieve monthly sales of ₹100,000 or more.

Use the IF function to calculate the incentive amount.

Practical Data:

Employee NameMonthly Sales
Amit125000
Priya85000
Rahul100000
Neha95000
Karan140000

Assume monthly sales are stored in B2.

Excel Formula:

=IF(B2>=100000,5000,0)

Enter the formula in C2 and copy it down.

Expected Result:

Employee NameMonthly SalesIncentive
Amit1250005000
Priya850000
Rahul1000005000
Neha950000
Karan1400005000

Explanation:

The IF function checks whether monthly sales are at least ₹100,000. If the condition is TRUE, Excel returns 5000. Otherwise, it returns 0.

Student Practice Tasks

Try these exercises yourself using the IF function.

Task 1: Student Result

A student passes if their marks are 35 or more. Display Pass or Fail.

Student NameMarks
Ankit45
Simran30
Mohit65
Riya34

Task 2: Employee Leave Eligibility

An employee is eligible for a special leave benefit if they have completed at least 12 months in the company.

Employee NameMonths Completed
Aman15
Pooja8
Varun12
Meena10

Display Eligible or Not Eligible.

Task 3: Product Reorder Status

A product needs reordering when its stock quantity is less than 10.

ProductStock
Keyboard15
Mouse5
Monitor8
Printer20

Display Reorder Required or Stock Sufficient.

Task 4: Sales Commission Eligibility

A salesperson receives a commission if monthly sales are greater than ₹75,000.

SalespersonSales
Amit90000
Priya70000
Rahul75000
Neha85000

Display Commission Eligible or Not Eligible.

Task 5: Check Minimum Order Amount

A company accepts free delivery for orders of ₹2,000 or more.

CustomerOrder Amount
Rohan2500
Sneha1500
Karan2000
Pooja1800

Display Free Delivery or Delivery Charges Apply.

Final Student Challenge: Create an Employee Performance Report

Problem Statement:

You are working in the HR department of a company. Management wants an employee performance report based on monthly sales.

Use the IF function to calculate the incentive and display whether each employee has achieved the sales target.

Company Data:

Employee IDEmployee NameMonthly Sales
EMP801Amit Sharma125000
EMP802Priya Verma85000
EMP803Rahul Kumar110000
EMP804Neha Singh95000
EMP805Karan Gupta150000
EMP806Pooja Mehta75000

Company Rules:

  • Monthly sales target: ₹100,000.
  • Incentive: ₹5,000 for employees achieving the target.
  • Employees with sales below ₹100,000 receive no incentive.

Your Tasks:

  1. Display Target Achieved or Target Not Achieved.
  2. Calculate the employee incentive.
  3. Create a final report showing employee name, monthly sales, target status, and incentive.

Expected Result:

Employee NameMonthly SalesTarget StatusIncentive
Amit Sharma125000Target Achieved5000
Priya Verma85000Target Not Achieved0
Rahul Kumar110000Target Achieved5000
Neha Singh95000Target Not Achieved0
Karan Gupta150000Target Achieved5000
Pooja Mehta75000Target Not Achieved0

Hint: Use one IF formula for the target status and another IF formula for the incentive amount.

Key Takeaways

  • The IF function is a logical function in Excel.
  • It checks whether a condition is TRUE or FALSE.
  • It returns one result when the condition is TRUE and another when it is FALSE.
  • The IF function uses three arguments: logical_test, value_if_true, and value_if_false.
  • Comparison operators such as greater than, less than, and equal to can be used in the logical test.
  • IF can return text, numbers, or calculated results.
  • It is useful for employee reports, student results, sales targets, attendance, and payment tracking.

Frequently Asked Questions (FAQs)

1. What is the IF function in Excel?

The IF function checks a condition and returns one value if the condition is TRUE and another value if it is FALSE.

2. What is the syntax of the IF function?

The syntax is:

=IF(logical_test, value_if_true, value_if_false)

3. Can the IF function return text?

Yes. The IF function can return text such as Pass, Fail, Eligible, Not Eligible, Yes, or No. Text results must be enclosed in double quotation marks.

4. Can the IF function return numbers?

Yes. The IF function can return numbers such as 0, 100, or 5000 depending on the condition.

5. What happens if the IF condition is FALSE?

Excel returns the value specified in the third argument, value_if_false.

6. Can I use greater than or less than operators inside IF?

Yes. You can use comparison operators such as >, <, >=, <=, =, and <> inside the logical_test argument.

7. Can the IF function compare two cells?

Yes. For example:

=IF(A2>=B2,"Achieved","Not Achieved")

This compares the values in A2 and B2.

8. Can the IF function be used to calculate bonuses?

Yes. You can use IF to calculate bonuses, incentives, commissions, and other amounts based on conditions.

9. Can the IF function check text values?

Yes. For example:

=IF(A2="Paid","Completed","Pending")

This checks whether cell A2 contains the text Paid.

10. Is the IF function case-sensitive?

No. A standard IF text comparison is not case-sensitive. For example, Paid and PAID are treated as equal in a normal comparison.