The IFS function in Excel is a logical function that allows you to test multiple conditions and return a value corresponding to the first TRUE condition. It is an alternative to nested IF statements, making complex logical evaluations simpler and more readable. The IFS function is particularly useful when you need to evaluate more than two conditions in a single formula.
Syntax
| Formula | Description |
|---|---|
| =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...) | Tests multiple conditions and returns a value corresponding to the first TRUE condition. |
- logical_test1, logical_test2, ...: The conditions you want to test.
- value_if_true1, value_if_true2, ...: The value to return if the corresponding logical test is TRUE.
Example
Let's consider a scenario where a company needs to categorize employees based on their years of service. The categories are as follows:
- Less than 2 years: "New"
- Between 2 and 5 years: "Experienced"
- More than 5 years: "Veteran"
| Employee | Years of Service | Category |
|---|---|---|
| John | 1 | =IFS(B2<2, "New", B2<=5, "Experienced", B2>5, "Veteran") |
| Jane | 3 | =IFS(B3<2, "New", B3<=5, "Experienced", B3>5, "Veteran") |
| Mark | 7 | =IFS(B4<2, "New", B4<=5, "Experienced", B4>5, "Veteran") |
In this example, the IFS function categorizes each employee based on their years of service. The formula checks each condition in order, and as soon as it finds a TRUE condition, it returns the corresponding category.
Practice Exercise 1
Task: Use the IFS function to assign letter grades to students based on their scores. The grading criteria are as follows:
- 90 or above: "A"
- 80 to 89: "B"
- 70 to 79: "C"
- 60 to 69: "D"
- Below 60: "F"
Hint: Start by defining the conditions for each grade in the IFS function, checking from the highest grade to the lowest.
Practice Exercise 2
Task: Use the IFS function to determine the performance bonus category for sales representatives based on their sales. The bonus categories are as follows:
- $100,000 or more: "High Bonus"
- $75,000 to $99,999: "Medium Bonus"
- $50,000 to $74,999: "Low Bonus"
- Less than $50,000: "No Bonus"
Hint: Set up the conditions in the IFS function to check the sales amount against the thresholds defined for each bonus category.
Solution for Exercise 1:
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")Explanation: This formula checks the student's score against the criteria for each grade, starting from the highest (A) and moving to the lowest (F). The first TRUE condition will return the corresponding grade.
Solution for Exercise 2:
=IFS(B2>=100000, "High Bonus", B2>=75000, "Medium Bonus", B2>=50000, "Low Bonus", B2<50000, "No Bonus")Explanation: This formula categorizes the sales representatives based on their sales, checking from the highest bonus category to the lowest. The first TRUE condition determines the bonus category that is assigned.