How to use excel IFS function

Excel Version in which IFS function can work:

Excel IFS function will work in Excel 2016, Excel Online, Excel for Android tablets, Excel Mobile, Excel for Android phones.

About excel IFS function:

Excel IFS function checks multiple condition and returns value for first TRUE condition.
IFS function can be used instead of using nested if function to check multiple if condition in one formula.
This makes formula easy to understand, you can enter up to 127 conditions in one formula.
Disadvantage of this function is that we can’t define the ELSE condition.
It will give #N/A error if non of the conditions are TRUE.

Syntax for IFS:

=IFS (logical_test1, value_if_true1,[logical_test2, value_if_true2], …)

logical_test1: The first condition you want to test.
value_if_true1: Result if the first condition is true
logical_test2: The second condition you want to test.
value_if_true2: Result if the second condition is true

This function will check the conditions in order and will return the value for the first condition which is true.

Using IFS function:

As mentioned above, we can use this function instead of nested if function where there are many if conditions to be tested.

In this example we will check how to calculate the grade for marks obtained with IFS function.

excel ifs function
excel ifs function
Subject Marks Formula Results
Subject 1 80  =IFS(B14>=70,”Distinction”,B14>=60,”First Class”,B14>=50,”Second Class”,B14>=40,”Pass”,B14<40,”Fail”) Distinction
Subject 2 61  =IFS(B15>=70,”Distinction”,B15>=60,”First Class”,B15>=50,”Second Class”,B15>=40,”Pass”,B15<40,”Fail”) First Class
Subject 3 39  =IFS(B16>=70,”Distinction”,B16>=60,”First Class”,B16>=50,”Second Class”,B16>=40,”Pass”,B16<40,”Fail”) Fail

Since this function does not have ELSE condition, If the formula does not contains collectively exhaustive list of conditions, this will return #N/A error in case non of the conditions are TRUE.

In such case we can define TRUE as last condition and mention the result for ELSE condition.

excel ifs function

Add a Comment

Your email address will not be published. Required fields are marked *