The IFS function in Excel is a useful tool for evaluating multiple conditions and returning a value corresponding to the first true condition. This function is an improvement over using several nested IF statements, making your formulas more readable and efficient. The IFS function is available in Excel 2016 and later versions.
Here’s how to use the IFS function:
Syntax:
=IFS(condition1, value_if_true1, [condition2, value_if_true2], ..., [condition_n, value_if_true_n])
Arguments:
- condition1, condition2, …: These are the conditions you want to evaluate. Each condition must be a logical test that returns TRUE or FALSE.
- value_if_true1, value_if_true2, …: These are the values that are returned when the corresponding condition is TRUE.
Steps to Use the IFS Function:
=IFS(A1 < 10, "Low", A1 < 20, "Medium", A1 >= 20, "High")
- Select a Cell: Click on the cell where you want the result to appear.
- Start Typing the Function: Enter `=IFS(` to initiate the function.
- Enter Conditions and Results:
- Provide the first condition you want to test, followed by the value to return if that condition is TRUE.
- For example, if you want to check if a cell (say A1) is less than 10 and return “Low”, the input would be `A1 < 10, “Low”`.
- Add additional conditions and corresponding values as needed. For instance:
- Close the Function: Once all conditions and results are specified, close the function with a parenthesis `)`.
- Press Enter: Hit Enter to complete the function and see the result in the selected cell.
Example:
Assume you have a student grading system where scores are in column A, and you want to assign letter grades in column B. You might use the IFS function as follows:
=IFS(A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C", A1 >= 60, "D", TRUE, "F")
Notes:
- Catch-all Condition: Like in the example above, using `TRUE` at the end ensures there’s a catch-all condition; if none of the previous conditions are true, this condition will be met by default.
- Evaluate in Order: The IFS function evaluates conditions in the order they are written. Once it finds a condition that evaluates to TRUE, it returns the corresponding result and stops evaluating further conditions.
- Error for No TRUE Condition: If none of the conditions evaluates to TRUE and there’s no catch-all condition, Excel will return an error.
Using the IFS function can significantly simplify your formulas, especially when dealing with multiple conditions.
Unlock Your Potential
Excel
Access
Power BI
