IFS Function
Returns a value that meets the first TRUE condition
Returns a value that meets the first TRUE condition
The IFS Function in Excel is a Logical function that was introduced in Excel 2016. The function is an alternative to the Nested IF function and is much easier to use. The IFS function checks if one or more than one conditions are observed or not and accordingly returns a value that meets the first TRUE condition.
= IFS(logical_test1, Value1 [logical_test2, Value2] …, [logical_test127, Value127])
Where:
The rest of the logical_test and Value arguments are optional; the function allows the user to put 127 logical_test arguments using the IFS function.
It is a built-in function which can be used as a worksheet function in Excel. Let’s take an example:
Let’s assume we wish to assign grades to marks earned by students. We can use the IFS Function in the following manner:
The formula used is:
IFS(A2>80,”A”,A2>70,”B”,A2>60,”C”,A2>50,”D”,A2>40,”E”,A2>30,”F”), which says that if cell A2 is greater than 80 then return an “A” and so on.
Using this formula, the result would be:
To understand the uses of this function, let’s consider few examples:
Let’s assume we have a list of items and we need to classify them under three common headings: Vegetable, Fruit, Green Vegetable and Beverage. When we use the IFS function and give the formula =IFS(A2=”Apple”,”Fruit”,A2=”Banana”,”Fruit”,A2=”Spinach”,”Green Vegetable”,A2=”coffee”,”Beverage”,A2=”cabbage”,”Green Vegetable”,A2=”capsicum”,”Vegetable”)
Then, we will get the following result:
In this example, we have entered multiple logical tests in the IFS function. When a logical test evaluates to TRUE, the corresponding value will be returned. However, if none of the logical tests evaluate to TRUE, then IFS function would give #N/A error. This is what we got for Pepper in B8.
Using ELSE function, we can ensure that this #N/A error is removed. For this we would place a final logical test at the end of the formula that is TRUE and then place a value that shall be returned.
Here, we added TRUE, “Misc”, this will ensure that Excel returns the value “Misc” in the scenario where none of the previous logical tests in the IFS function evaluates to TRUE.
Now the results would as be as shown below:
Prior to the introduction of the IFS function, we used to use nested IF function. Let’s see how the IFS function is superior to nested IF. Let’s assume we wish to give discounts to customers based on their total purchases from us. So, a customer would get 5% discount for purchases more than $100 but less than $500, 10% discount if he made purchases of more than $500 but less than $750, 20% if he made purchases of more than $750 but less than $1,000 and 30% for all purchases above $1,000.
Let’s see the formula when we would have used nested IF function:
Now, let’s see how it becomes easy to write the formula for same results using IFS:
Hence, IFS is easier as it allows using a single function to input a series of logical tests. It becomes cumbersome in the nested IF function, especially when we use a large number of logical tests.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!