Conditional functions like IFS have the potential to take your Excel formulas to the next level. With IFS, you can create conditions and nest different functions inside one another in a single formula. Alternatively, you can also use the IFS function on its own.
But what is the IFS function and how is it different from the ordinary IF function? Read on to find out what IFS in Excel is and how you can use it.
What is the IFS function in Excel?
The IFS function in Excel takes values and compares them to specific conditions. If the value meets the first condition, then the formula stops there and returns an output. But, if the first condition is not met, the formula will continue to test the conditions in sequence until one condition is met.
= SIF ([Something is True1, Output if True1,Something is True2,Output if True2,Something is True3,Output if True3)If the value doesn’t fit any of the conditions, then the formula returns an N/A error. However, you can fix this by entering a final condition such as TRUE and an output for that condition like “Value doesn’t meet any of the conditions”. The TRUE condition makes sure that any value meets at least the final condition, so that you don’t get the N/A error.
How to Use the IFS Function in Excel
IFS is an upgraded version of the IF function in Excel. The syntax and the usage are similar, except that IFS is designed to take in multiple conditions. The IF function required nesting multiple IF functions to define multiple conditions, while the IFS can define it all with a single function.
The first parameter in IFS will be the condition, and the second parameter will be the output if that condition is met. The third parameter will be condition number 2, and the fourth parameter will be the output if condition number 2 is met.
The parameters are coupled this way, and you can enter up to 127 conditions in a single IFS function. You can, of course, also enter another function in the output and create compound nested formulas. If you’ve worked with IF and nested IF formulas, then using IFS will be a breeze for you. IFS is here to make things easier, so let it do that!
Single Condition IFS Function Example in Excel
Reading about the IFS function won’t do you any good until you put it to practice. So let’s get you started with an example. Despite the fact that the IFS function can take in multiple conditions, that doesn’t mean that you can’t use it for single-condition scenarios.
Let’s write an IFS formula with a single condition, and even include some other functions in it to get the hang of things.
In this example, we have some products and their years of production. These imaginary products spoil two years after they are produced, so we are going to create a formula that determines if the products are expired or not. Effectively, we will use the IFS function in conjunction with the YEAR and TODAY functions.
The YEAR function returns the year from a specific date. We are going to provide the YEAR function with today’s date using the TODAY function, so that the output of the YEAR(TODAY()) complex is directed to the current year. Let us begin.
- Select the first cell where you want to output the results. We are going to use the first cell under the Expired column.
- In the formula bar, enter the following formula:
=IFS(YEAR(TODAY())-B2<=2,"No",TRUE,"Yes")This formula calls the IFS function to check if the value in the cell B2 is 2 or less. This is done by subtracting the production date from the current year. If the condition is met, then the product is not expired and Nope it is returned. Anything other than this would mean the product is expired, so the output to CERTAIN is set to Yes.
- Press Get into.
Now you will see if the first product is expired or not. All you need to do from here is grab the fill handle and drop it onto the cells below. You can also replace YEARS (TODAY()) with the simple number of the current year, if you don’t intend to use the formula in the future.
Example of multiple condition IFS function in Excel
Now let’s get to work: the IFS function with multiple conditions. For this purpose, we are going to use an example task that we used earlier with nested IF statements.
In this example, we have a table that contains the names of some cars and their acceleration times. We also have a list that determines which class each car belongs to based on its acceleration time from 0 to 100. The goal is to create a formula that takes each car’s acceleration time into account and then displays its class in the table.
There is also a class guide table so you know how the ranking system works. Ultimately, we are going to create a formula using IFS based on this tutorial. The formula will take the acceleration time, judge it, and then generate the class. If the car takes more than 14 seconds to accelerate, it will be considered too slow.
This table is made for the purpose of this article, so don’t take it too seriously. With that, let’s begin.
- Select the first cell where you want to display the result of the IFS function. In this example, that will be the first cell in the Class column.
- Type the following formula in the formula bar:
=IFS(B2<3, "S", B2<5, "A", B2<7, "B", B2<9, "C", B2<10, "D", B2<12, "E", B2<14, "F", TRUE, "Too slow!")This formula calls the IFS function to take the value of the acceleration time in the cell B2 and check if it is less than 3 seconds. If so, then issue yes like class. If not, the formula goes to the next condition. This will continue until one of the conditions is met. Since our formula covers all values, we won’t get the N/A error.
- Press Get into.
Once you press Get into on your keyboard, you will instantly see the class of the first car. Now all you need to do is grab the fill handle and drop it on the cells below to determine the class of those cars as well. You can even add some conditional formatting so that the class cells have different colors based on their value.
Excel made easier with IFS
IFS is similar to IF in essence, in that both Excel functions take values and test them against custom conditions. What IFS does better than IF, is multiple conditions. With the IF function, you would need to nest multiple functions inside each other to declare multiple conditions, whereas with IFS, you can do it in a single function.
Conditions are game changers in Excel. You can include other functions in the IFS formula to create complex formulas that allow you to achieve crazy things in Excel. Now that you know about the IFS feature, it’s time to go ahead and explore the endless possibilities this feature has to offer!
