Use IFS Function in Excel 2016 Instead of Nested IF

If you use excel 2016 you can apply IFS function. As we discussed before that IF function can be used for a condition which return one value if condition is true and another value if condition is false. It means you can return only two values using IF function. But if you need to return more than two values you can use IFS function which is new in excel 2016. It allows you to test up to 127 different conditions. Before Nested IF was used for the purpose of multiple conditions in older versions of excel (2007, 10, 13) which is now outdated.

In this tutorial we will learn the syntax of IFS function and then look some examples.

Syntax:

=IFS(condition1, value1, condition2, value2, … condition127, value127)

Arguments:

condition1: It refers to the first condition to be evaluated.

value1: It refers to the return value if condition is TRUE.

condition2, value2: [optional] Second condition and second value and so on…. up to 127.

Example 1 - Determine Grades Using IFS Function

Suppose you are giving grades to students on the basis of obtain marks in the examination. For this you made a condition if a student obtains 80 or above marks, he will be given A1 grade. If he obtains less than 80 marks he will be given A grade. If he obtains less than 70 marks, he will be given B grade. If he obtains less than 60 marks, he will be given C grade. If he obtains less than 50 marks, he will be given D grade. And if he obtains less than 40 marks, he will be considered Fail. Let’s try this in excel.

The formula in cell D4 is:

=IFS(C4<40,''Fail'',C4<50,''D'',C4<60,''C'',C4<70,''B'',C4<80,''A'',C4>=80,''A1'')

It can be seen from the above table that excel returned A1, A, B, C, and D grades to those students having obtained marks above forty. On the other hand, excel returned, Fail to one student who has obtained marks less than forty. This is because of using IFS function.

Example 2 - Invoice Status Using IFS and TODAY() Function

In this example we will draw a table that will determine invoice status of customers.  The table will contain invoice number, due date, amount, amount paid, open balance and invoice status. In the column of status, we will apply IFS function with TODAY() function. The TODAY() function is used for current date (e.g. today date). In my case the current date for this example is 18 October 2017.

The formula in cell G5 is:

=IFS(F5=0,''Paid'',TODAY()<C5,''Open'',F5<>0,''Overdue'')

We can see in the status column that excel return ‘Paid’ to those customers who have paid before current date (18 October 2017). The ‘Open’ customers are those to whom the amount is not yet due.  It can be seen from the above table that excel return ‘Overdue’ to those customers who have not paid the amount in full. This work is only done by using IFS function with TODAY() function.