In this article, we will let you know how to use IFS function in Excel along with the figures, tips and steps in creating formulas, as you work on your spreadsheet.
The IFS function formula is mostly used in analyzing logical statements in worksheets. Additionally, it evaluates multiple conditions and returns a specified value of a condition given by the criteria.
Furthermore, IFS functions are more practical than regular IF functions (i.e., true or false) for performing several logical tests and helping users organize their data by specific criteria.
what does the ifs function do in excel?
The main function of IFS formula is to assess one or more conditions criteria along with the return value evaluating the true condition. Users can determine which values the function returns upon each condition.
Thus, if the condition is False, the second condition turns the function to evaluate. Meanwhile, if the second condition is false, it proceeds to the condition and evaluates, its go-along in the final condition.
However, if the condition turns true, it will return the value stated on the condition. In addition to this, you can enter up to 127 conditions, also known as logical tests, for the function to evaluate.
Furthermore, it was only introduced in 2016. So, prior to this function users used IF function to evaluate IF condition and return a specific value. Though IF function can evaluate multiple conditions, it is more practical to use IFS functions in more logical tests.
Other than that, the IFS function is easier to read, as all you have to do is enter conditions followed by their true values instead of using multiple IF functions within a single IF function.
excel ifs formula
Here’s the Excel ifs formula:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3; ...)
Arguments
Here are the arguments for this function:
logical_test1– It is the first logical test.value_if_true1– The result when test1 is TRUE.logical_test2,value_if_true2– [optional] Second test/value pair.
Return Value
The value corresponds with the first condition that evaluates to true result.
ifs statement in excel
Example 1: Grades, Lowest to highest
This example will guide you if you want to organize students’ grades in the grading workbook. Take a look at this scale as we go further we used it in our excel formula.
| Condition | Scale |
| =>90. Equal to or greater than 90 | Very Good |
| =>80. Equal to or greater than 80 | Good |
| >=70. Equal to or greater than 70 | Poor |
| >=60. Equal to or greater than 60 | Fail |
Below we will try this using the IFS function in the grading scale. When you enter the logical tests and their corresponding values, the function checks your students’ grades and returns the appropriate scale.
For instance, a student who got a grade of 75 yields Poor. For the grade of 50, the function returns “Failed”.
Example 2:
Here we will explore the example below. The example was assigned in a grade based on score they have. The formula in H5 is shown below:
=IFS(F2<60,"Failed",F2<70,"Poor",F2<80,"Good",F2<90,"Very Good",F2>=90,"Outstanding")
Notice the conditions are entered “in order” to test lower grades first. The grade associated with the first test to return TRUE is returned.
how to use the ifs function in excel
Here are the ways how to use the IFS function in Excel
1. Enter the IFS function
Find a cell, click where you want to place your function, and return the corresponding value. Then Type =IFS where you can choose it from a dropdown menu.
Alternatively, you can find it in the function toolbar and search the IFS function while scrolling the menu or using the search bar.
2. Create the first logical test
After you enter the IFS function, you can create your first logical test if you want the function to perform after the open parenthesis, as shown in the figure below.
Here are some logical operators you can use.
- Equal to (=)
- Not equal to (<>)
- Less than or equal to (<=)
- Greater than or equal to (>=)
- Less than (<)
- Greater than (>)
A lot of people use this IFS function to determine the relationship of a value from a given number. For instance, if you want to determine whether the value of F2 is less than the value of 60, the first logical test would be F2<60.
Nonetheless, if you plan to copy the formula to other cells but want to keep comparing other cell values to F2, fix F2 by typing F2>$F$F instead of F1>F2.
3. Enter the first value if true
This time put a comma in after you are done with your first logical test, and enter the value you want to output if the condition is true. Thus, if you want your value to be a number, just enter it and put a comma after it.
Anyway, if you want a text like in our example simply enclose it with double quotes and then put a comma after it. For instance, if you want Excel to return a value of “Failed” if F2 is less than 60, the beginning of your IFS function would look like this:
=IFS(F2<60, "Failed", )
4. Enter more logical tests
After you input the first logical test and value, you can able to add more logical tests if you want. As you know the IFS function only requires one logical test and value if true. Once you’re done, put a closing parenthesis at the end of the final value if true.
Here’s an example IFS function with multiple logical tests:
=IFS(F2<60,"Failed",F2<70,"Poor",F2<80,"Good",F2<90,"Very Good",F2>=90,"Outstanding")
how to write a nested if function in excel
In our prior tutorial, we mentioned there the Nested if statements function and how it is utilized. Now let’s see how superior it is or what makes it different from IFS function.
Supposedly, we wish to give higher points to students based on their performance. So, a student would get 5% extra points.
Let’s see the formula when we would have used Nested IF function:
Now, let’s see how it becomes easier to write the formula for the same results using IFS:
Hence, IFS is easier, as it allows using a single function to input a series of logical tests. It becomes unmanageable in the nested IF function, especially when we use a large number of logical test arguments.
Is there a median if function in excel?
Fortunately, there is a median IF array however will venture into another article specifically discussing it.
But it is defined as an array formula that identifies the middle number of values that meet certain criteria. An array formula performs an operation on multiple values instead of a single value.
Summary
In summary, the discussion about how to use IFS function in Excel ponders the following:
- When the value returns #VALUE! error logical test does not return TRUE or FALSE value.
- #N/A error defines that there is no logical test return TRUE.
- The IFS function does not have a built-in default value to use when all conditions are FALSE.
- To provide a default value, enter TRUE as a final test, and a value to return when no other conditions are met.
