IFS Function in Excel: Explained
In this article, you will learn how to use the IFS formula in Excel.
What does the IFS formula do in Excel?
The IFS formula in Excel is a logical function that allows you to evaluate multiple conditions and returns a value based on the first condition that evaluates to true. It is an alternative to using nested IF statements. The IFS formula allows you to handle multiple conditions and returns different values based on the outcome. This can simplify complex nested IF statements and make formulas more concise and easier to understand.
How to use the IFS function in Excel?
The syntax of the IFS formula in Excel is as follows:
Each "condition" is an expression which is evaluated as either true or false. If a condition is TRUE, the corresponding "value" is returned.
The formula evaluates the first condition. If it is true, the corresponding value is returned. If the first condition is false, the formula moves to the next pair of condition-value arguments and checks the second condition. If the second condition is TRUE, its corresponding value is returned. The process continues until a condition evaluates as TRUE, and the corresponding value is returned. If none of the conditions is met, the formula returns the #N/A error.
Note: The IFS formula requires Excel 2019 or later versions. If you're using an older version of Excel, you can achieve similar functionality using nested IF statements.
A sample use case for the IFS formula in Excel
Suppose you are an accountant responsible for calculating the bonus payouts for employees based on their performance ratings. The bonus payout structure is as follows:
Performance Rating of 1: No bonus
Performance Rating of 2: 2% of annual salary
Performance Rating of 3: 5% of annual salary
Performance Rating of 4: 8% of annual salary
Performance Rating of 5: 10% of annual salary
Step 1: Create a table with employee information, including their names, performance ratings, and annual salaries.
Step 2: Add a blank column to calculate the bonus payout for each employee using the IFS formula.
Step 3: In the "Bonus Payout" column, use the IFS formula to calculate the bonus payout based on the performance rating. For example, in Row 2 enter:
B2 refers to the cell containing the performance rating for the employee.
C2 refers to the cell containing the annual salary for the employee.
Step 4: Copy the formula down to apply it to all employees in the table.
The IFS formula evaluates each condition and returns the corresponding bonus payout based on the performance rating. If the performance rating is 1, the formula returns a bonus payout of 0. If the performance rating is 2, it calculates 2% of the annual salary, and so on.
This allows you to automate the calculation of bonus payouts based on different performance ratings, saving time and reducing errors in the process.
