Excellency

View Original

The IFS function

The IFS function is a logical function which is very similar to its younger brother, the IF function, with the difference being that it allows you to create a string of IF statements which will be evaluated one after the other until one of the conditions has been met.

This is the format of the IFS function:

=IFS(LOGICAL TEST 1, RESULT 1, LOGICAL TEST 2, RESULT 2, LOGICAL TEST 3, RESULTS 3, etc…)

Here is an example of the IFS function:

=IFS(1+1=3, 1, 1+1=1, 2, 1+1=2, 3) = 3 

IFS function evaluating through boolean equations until a value of TRUE and returning the appropriate result

This function is valuable when you have a finite (and relatively short) list of conditions to check against, that have a set of predefined responses when those conditions are met. This could be calculating the results of a survey or text, a checklist assessment, or variable slider results from a set of multiple choices.

An example of a functional implementation of this would be taking the results of a company wide survey and assigning attributes to employees through their responses. Some examples of this are how personality test results are calculated. Take a simplified risk profile assessment. The respondents scored each statement from 1-10 and based on the average score, you determine their profile.

=IFS(AVERAGE SCORE > 7, “High risk tolerance”, AVERAGE SCORE > 4, “Medium risk tolerance”, AVERAGE SCORE >= 0, “Low risk tolerance”)