What is the Excel IFS Function?
Excel IFS Function is an improvement of multiple IF statements. No need nested IF function to analyze more than two criteria, one IFS function is enough but with many logical_test.
Unfortunately, this function is only available for those who subscribe to Office 365. For those who have no Office 365 subscription, please use multiple IF statements or other IFS function alternatives.
IFS(logical_test1, value_if_true1 , [logical_test2, value_if_true2] , [logical_test3, value_if_true3] , …)
logical_test1 (required), the first condition to be analyzed, whether in accordance with the criteria specified. The result is TRUE / FALSE.
value_if_true1, the returned value if logical_test1 returns a TRUE value
- The IFS function able to analyze up to 127 criteria, of course, this is an improvement, nested IF statement only able to accommodate up to 64 IF functions in a formula (before Excel 2007 even only 7 IF functions).
- IFS function has no value_if_false argument, meaning if there are six criteria, then there are six logical_test, in contrast to nested IF function if there are six criteria then there are only five logical_test.
- If no logical_test returns a TRUE value, then the IFS function returns a #N/A error.
There are six weight category according to BMI value.
Here is a formula to answer the questions above.
=IFS(D2<18.5,"Underweight" ,D2<25,"Normal" ,D2<30,"Overweight" ,D2<35,"Class I Obesity" ,D2<40,"Class II Obesity" ,TRUE,"Class III Obesity")
logical_test6 filled with TRUE as a replacement for value_if_false argument in nested IF statements, value_if_true6 will be the result of IFS function if logical_test1 until logical_test5 return FALSE.
Here is the step by step on how to use IFS function.
Another Alternative for IFS Function