How to Use the IFS Function in Excel
The IFS function in Excel and Google Sheets is a logical function designed to evaluate multiple conditions sequentially and return a value corresponding to the first condition that is true. It is particularly effective when dealing with several potential conditions, each leading to a different outcome.
Basic Syntax:
The basic syntax for the IFS function is as follows:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Examples:
Here are some practical examples to illustrate how the IFS function operates:
Example 1 – Grading System:
Imagine a grading system with these criteria:
- Score < 50: Fail
- 50 <= Score < 70: Pass
- 70 <= Score < 90: Good
- Score >= 90: Excellent
To allocate grades based on the student’s score, you can use the IFS function as shown:
Score | Grade |
---|---|
65 | =IFS(A2<50, “Fail”, A2<70, “Pass”, A2<90, “Good”, A2>=90, “Excellent”) |
Example 2 – Categorizing Sales Data:
Next, consider categorizing sales data into segments:
- Sales < 1000: Low
- 1000 <= Sales < 5000: Medium
- Sales >= 5000: High
The IFS function can be applied to classify each sales figure into the appropriate category:
Sales | Category |
---|---|
3500 | =IFS(A2<1000, “Low”, A2<5000, “Medium”, A2>=5000, “High”) |
These examples clearly show how the IFS function can efficiently process multiple conditions to yield the relevant results for each scenario.
More information: https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45