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 Name | Test Score |
|---|---|
| John | 75 |
| Sarah | 50 |
| Tom | 85 |
| Emma | 60 |
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 Name | Test Score | Result |
|---|---|---|
| John | 75 | Pass |
| Sarah | 50 | Fail |
| Tom | 85 | Pass |
| Emma | 60 | Pass |
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.
| Person | Age |
|---|---|
| John | 28 |
| Sarah | 16 |
| Tom | 72 |
| Emma | 42 |
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:
| Person | Age | Age Group |
|---|---|---|
| John | 28 | 18-64 |
| Sarah | 16 | Under 18 |
| Tom | 72 | Over 64 |
| Emma | 42 | 18-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.
| Employee | Sales |
|---|---|
| John | 8500 |
| Sarah | 12500 |
| Tom | 9800 |
| Emma | 7500 |
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:
| Employee | Sales | Bonus |
|---|---|---|
| John | 8500 | 0 |
| Sarah | 12500 | 625 |
| Tom | 9800 | 490 |
| Emma | 7500 | 0 |
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:
| Data | Result |
|---|---|
| 123 | Not Blank |
| Blank | |
| ABC | Not 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.
| Task | Status |
|---|---|
| Task 1 | Completed |
| Task 2 | In Progress |
| Task 3 | Not Started |
| Task 4 | Completed |
| Task 5 | Completed |
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:
| Task | Status | Score |
|---|---|---|
| Task 1 | Completed | 1 |
| Task 2 | In Progress | 0.5 |
| Task 3 | Not Started | 0 |
| Task 4 | Completed | 1 |
| Task 5 | Completed | 1 |
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.
