The Excel IFS function tests a number of supplied conditions and returns the result corresponding to the first condition that evaluates to TRUE. If none of the supplied conditions evaluate to TRUE, the function returns the #N/A error.
The syntax of the IFS function is:
where the arguments are as follows:
|-||The conditions that are to be tested and evaluated as either TRUE or FALSE.|
|-||The results that are to be returned if the corresponding logical_test evaluates to FALSE.|
The following Ifs function examples return:
|1||5||4||=IFS( B1>1, A1/B1, B1<-1, -A1/B1, TRUE, 0 )||- returns the value 1.25|
|2||5||-2||=IFS( B2>1, A2/B2, B2<-1, -A2/B2, TRUE, 0 )||- returns the value 2.5|
|3||5||0||=IFS( B3>1, A3/B3, B3<-1, -A3/B3, TRUE, 0 )||- returns the value 0|
Note that the final condition of the above Ifs functions is simply "TRUE". As this always evaluates to TRUE, the corresponding value, 0, is returned whenever neither of the prior conditions evaluate to TRUE (as in cell C3).
If we had not included this final condition, the example in cell C3 would have returned the #N/A error.
The Ifs function greatly simplifies what previously might have been multiple nested If functions.
For example, prior to Excel 2016, you might have used the following nested if function:
|=IF(A1=1, "Sun", IF(A1=2, "Mon", IF(A1=3, "Tue", IF(A1=4, "Wed", IF(A1=5, "Thu", IF(A1=6, "Fri", IF(A1=7, "Sat", "")))))))|
In Excel 2016, this can be replaced by the following single call to the Ifs function which avoids the repeated brackets, and is therefore easier to type and less prone to errors:
|=IFS( A1=1, "Sun", A1=2, "Mon", A1=3, "Tues", A1=4, "Wed", A1=5, "Thu", A1=6, "Fri", A1=7, "Sat" )|
For further details and examples of the Excel Ifs function, see the Microsoft Office website.
If you get an error from the Excel Ifs function, this is likely to be one of the following:
|#N/A||-||Occurs if none of the supplied logical_tests evaluate to TRUE.|
|#VALUE!||-||Occurs if one or more of the supplied logical_tests returns any value other than TRUE or FALSE.|
|#NAME?||-||Occurs if you are using an older version of Excel (pre-2016), that does not support the Ifs function.|