Share this article:

The IF statement is one of the most powerful and commonly used functions in Excel. It allows you to test a condition and return a value based on whether the condition is true or false. In this article, we will discuss five examples of using an IF statement in Excel, along with the data used and the result.

Example 1: Testing for a Pass or Fail Grade

Suppose you have a list of students and their test scores, and you want to determine whether each student has passed or failed the test based on a passing score of 60.

Student NameTest Score
John75
Sarah50
Tom85
Emma60

To determine whether each student has passed or failed, you can use the following formula in column C:

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

This formula checks whether the test score in column B is greater than or equal to 60. If the condition is true, the formula returns “Pass.” If the condition is false, the formula returns “Fail.” The result will be as follows:

Student NameTest ScoreResult
John75Pass
Sarah50Fail
Tom85Pass
Emma60Pass

Example 2: Categorizing Age Groups

Suppose you have a list of people and their ages, and you want to categorize each person into one of three age groups: under 18, 18-64, or over 64.

PersonAge
John28
Sarah16
Tom72
Emma42

To categorize each person into an age group, you can use the following formula in column C:

=IF(B2<18,”Under 18″,IF(B2<=64,”18-64″,”Over 64″))

This formula first checks whether the age in column B is less than 18. If the condition is true, the formula returns “Under 18.” If the condition is false, the formula checks whether the age is less than or equal to 64. If this condition is true, the formula returns “18-64.” If both conditions are false, the formula returns “Over 64.” The result will be as follows:

PersonAgeAge Group
John2818-64
Sarah16Under 18
Tom72Over 64
Emma4218-64

Example 3: Calculating a Bonus

Suppose you have a list of employees and their sales, and you want to calculate a bonus for each employee based on their sales. If an employee’s sales are greater than or equal to $10,000, they will receive a bonus of 5% of their sales. Otherwise, they will not receive a bonus.

EmployeeSales
John8500
Sarah12500
Tom9800
Emma7500

To calculate a bonus for each employee, you can use the following formula in column C:

=IF(B2>=10000,B2*0.05,0)

This formula checks whether the sales in column B are greater than or equal to $10,000. If the condition is true

, the formula multiplies the sales by 5% to calculate the bonus. If the condition is false, the formula returns 0. The result will be as follows:

EmployeeSalesBonus
John85000
Sarah12500625
Tom9800490
Emma75000

Example 4: Checking for Blanks

Suppose you have a list of data that includes some blank cells, and you want to check for these blank cells.

Data
123
ABC

To check for blank cells, you can use the following formula in column B:

=IF(A2=””,”Blank”,”Not Blank”)

This formula checks whether the cell in column A is blank. If the condition is true, the formula returns “Blank.” If the condition is false, the formula returns “Not Blank.” The result will be as follows:

DataResult
123Not Blank
Blank
ABCNot Blank
Blank

Example 5: Creating a Progress Tracker

Suppose you are tracking the progress of a project that has multiple tasks. You want to create a progress tracker that shows the percentage of completed tasks.

TaskStatus
Task 1Completed
Task 2In Progress
Task 3Not Started
Task 4Completed
Task 5Completed

To calculate the percentage of completed tasks, you can use the following formula in column C:

=IF(B2=”Completed”,1,IF(B2=”In Progress”,0.5,0))

This formula checks the status in column B and assigns a score based on the status. If the status is “Completed,” the formula returns a score of 1. If the status is “In Progress,” the formula returns a score of 0.5. If the status is anything else, the formula returns a score of 0. The result will be as follows:

TaskStatusScore
Task 1Completed1
Task 2In Progress0.5
Task 3Not Started0
Task 4Completed1
Task 5Completed1

To calculate the percentage of completed tasks, you can use the AVERAGE function to calculate the average of the scores. For example, you can use the following formula in cell D1:

=AVERAGE(C2:C6)

This formula calculates the average of the scores in column C, which gives the percentage of completed tasks. The result will be as follows:

Total Progress
0.7

This formula can be very useful when tracking the progress of a project or any other task that has multiple steps. By using the IF statement, you can easily assign scores based on the status of each step, and calculate the overall progress.