In this tutorial, we will be discussing the use of the IFS() function in Excel. This function is particularly useful when you have multiple conditions that need to be tested and can save you a lot of time and effort compared to using nested IF statements. We will be covering how to use the IFS() function, its syntax, and some examples of how it can be applied in real-world scenarios.
Using the IFS function over the IF function
The IF function and the IFS function in Excel are used to test logical conditions and return a value based on the result of the test. The main difference between the two is the number of arguments they accept.
The IF function takes three arguments: a logical test, a value to return if the test is true, and a value to return if the test is false. The IFS function, on the other hand, takes multiple logical tests and corresponding values, and returns the value associated with the first true test.
When dealing with multiple conditions, the IFS function can test multiple conditions at once and return a value based on the first true condition, while the IF function can only test one condition at a time and requires more nested IF function statements to test multiple conditions.
Also, the IFS function is only available in Excel 2016 and later versions, while the IF function is available in all versions.
IFS function examples:
Example 1. In this example, we have a list of names with their names of students in the A column and their Math and English grades in the C and D column respectively . With this data, we want to automatically create a letter grading system: lower than 40 = F, 40-59 = D, 60-69 = C, 70-79 = B, 80-89 = A, 90 or more = AA. To do this, we will use the IFS function to nest these conditions.
- Choose the cell we want to show the letter grade.
- Type in “=IFS(” and begin defining your logical tests and corresponding values.
- The first test is “if this is less than 40,” then the corresponding value is “F” (fail), so in the formula write in: =IFS(C2<40,”F”…
- The next test is “if this is less than 60,” then the corresponding value is “D.” So the formula continues to: =IFS(C2<40,”F”,C2<60, “D”…
- This continues for other grades (E, C, B, A, AA). The formula should be: =IFS(A1<40,”F”,A1<60,”D”,A1<70,”C”,A1<80,”B”,A1<90,”A”,A1>=90,”AA”
- Be sure to close the parentheses after defining all the logical tests and values.
- Press enter and verify that the correct grade is returned.
- To apply the same formula to multiple cells, you can simply drag the formula to the desired cells or by double-clicking the lower-right corner of the selected cell.
Example 2. Using the same example, we want to create a remark based on their letter grade: AA = Topper; A = Excellent; B = Good; C = Average; D = Passed; F = Failed. To do this, we will use the IFS function to nest these conditions.
- Choose the cell we want to show the letter grade.
- Type in “=IFS(” and begin defining your logical tests and corresponding values.
- The first test is “if the letter grade is AA” then the remarks is value is “Topper”, so in the formula write in: =IFS(E2=”AA”,”Topper”…
- The next test is “if the letter grade is A,” then the remarks is “Excellent.” So the formula continues to: =IFS(E2=”AA”,”Topper”, E2=”A”,”Excellent”….
- This continues for other grades (E, C, B, A, AA). The formula should be: =IFS(E2=”AA”,” Topper”,E2=”A”,”Excellent”,E2=”B”,”Good”,E2=”C”,”Average”,E2=”D”,”Passed”,E2=”F”,”Failed”)
- Be sure to close the parentheses after defining all the logical tests and values.
- Press enter and verify that the correct remarks are returned.
Conclusion
The IFS function in Excel is a useful tool for evaluating multiple conditions and returning a corresponding value. It is an alternative to using multiple nested IF statements and can simplify your formulas. The basic syntax for the IFS function is “=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)”. The logical tests and corresponding values can be defined based on the specific needs of your spreadsheet. It is available in Excel 2016 and later versions.
