The IFS function gives you an efficient alternative to the tedious task of creating a nested IF function.
The IFS function checks conditions sequentially, and as soon as a condition is found to be true, the function stops and returns the specified value. You can take advantage of this logic to define a default result to be returned if no other conditions are met by defining the logical test for the last condition to always be true, such as logical_test=true. Because the condition is always true, its value will be returned for all input data that does not meet any of the other conditions.
In this tip, we demonstrate how you can use the new IFS function to calculate an incentive on product sales. The sales reps will get a 10% incentive for each sale over $500.
Applies To: Microsoft® Excel® 2016 Office 365 Pro Plus.
On the Formulas tab, in the Function Library group, click Logical, and select IFS from the menu.
In Logical_test1, we’ll specify if the value in the product sales column is greater than or equal to 500.
In Value_if_true1, we’ll specify what happens if the logical test is true, and the product sales value is higher than 500. In this case, the function needs to calculate 10% of the product sales value.
In Logical_test2 we need to specify if the value in the product sales column is less than 500.
In Value_if_true2 we’ll specify that the sales reps get no incentive if this logical test is true.
Click OK and copy the results down to the rest of the column.
Have a look at the results. Where the transactions are equal to or greater than $500, an incentive amount is calculated, and where not, Excel returns 0.
The post How to use the IFS Function in Microsoft® Excel® appeared first on Sage Intelligence.
Powered by WPeMatico