Excel IFS function

As we all known, we can use the Nested IF function to test whether multiple conditions are met. But here, the IFS function is much easier to use than the Nested IF function. IFS function is used to test multiple conditions and return a value corresponding to the first TRUE result, if none of the supplied conditions evaluate to TRUE, the function returns the #N/A error.

 Syntax:

The syntax for the IFS function in Excel is:

 Arguments:

• logical_test1: Required. The condition that evaluates to TRUE or FALSE.
• value_if_true1: Required. Returns the result if logical_test1 is TRUE. It can be empty.
• logical_test2: Optional. The second condition that evaluates to TRUE or FALSE.
• value_if_true2: Optional. Returns the second result if logical_test2 is TRUE. It can be empty.

 Return:

To test multiple conditions, and return corresponding value with first TRUE result.

 Examples:

Example 1: Use IFS functions for identifying the grade based on score

To assign the grade for each student based on their scores as following screenshot shown:

Please apply the below formula, so the grade will be categorized as this: 0-60: grade F; 60-70: grade D; 70-80: grade C; 80-90: grade B; greater or equal to 90: grade A.

Note: You can also use the cell references to replace the static numbers as this:

Example 2: Use IFS functions for calculating commission based on sales

The IFS function can help you to calculate commission amounts based on varying commission rates and cumulative sale totals for each month.

Supposing, you have a table with all salesmen’s total sales and commission rates as following screenshot shown, how could you calculate the commission based on the various commission rate in Excel?

The following formula can give the commission value for each sales personnel, please use this:

which means:

• if the sales total is 0-40000: commission= sales * 0;
• if the sales total is 40000-80000: commission= sales * 2%;
• if the sales total is 80000-100000: commission= sales * 3.5%;
• if the sales total is greater or equal to 100000: commission= sales * 7%;

Tips: The IFS function will get #N/A error, if none of the conditions evaluate to TRUE. If you want to replace the #N/A error by another meaningful value, you should add an ELSE condition at the end of the IFS function.

Normally, use the IFS function:

Use the IFS function with ELSE condition:

Note: In the above formula, the addition of the final condition, "TRUE","Others" will return the value "Others" when none of the conditions in the IFS function evaluate to TRUE.