One of the new functions released in Office 365 and Excel 2019 is the “IFS” function. It checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
It acts as the (IF <condition1> THEN <result1> ELSEIF <condition2> THEN <result2>…) structure in other programming languages. The beauty of this function is that it replaces the cumbersome, nested IF statements that turn into a monster of IF’s in some cases.
The new IFS function is simple to build and maintain. It evaluates a set of “condition-result” pairs from left to right. The result returned is that of the first condition turning TRUE. So, it is a viable alternative to using nested IF statement (IF inside another IF)
For example, calculating bonuses of a list of employees based on their performance evaluation results, as one condition, and based on their regional achieved target in sales, as another condition can be neatly written in a single IFS statement.
In this scenario, there are three conditions:
Condition 1:
Regardless of the regional achieved target, if the employee’s performance is “Need Improvement”, he will not get any bonus.
Condition 2:
Regardless of the employee’s performance, if the regional achieved target is below 50%, he will not get any bonus.
Condition 3:
If the employee’s performance is “Excellent”, he will get as 3 times as his monthly salary for annual bonus, provided that conditions 1 and 2 are not TRUE.
Condition 4:
If the employee’s performance is “Good”, he will get as 1.5 times as his monthly salary for annual bonus, provided that conditions 1 and 2 are not TRUE.
When using IFS to calculate the bonus, the function evaluates each condition independently from left to right. The outcome of the function is the result of the first TRUE condition.
NB: as usual, get used to the habit of naming the ranges or converting your data into Table for better legibility and easier error-correction.
See that in action with this video.
