[ad_1]
If you’re familiar with using the IF function in Excel, then you might be ready to check out the IFS function. With it, you can test multiple conditions at once, instead of using nested IF statements.
With IFS, you can test up to 127 conditions in a single Excel formula. Although this number of tests is probably much more than you need, the function is ideal for checking various conditions. The formula then returns the value you specify for the first true condition in the statement.
Use the IFS function in Excel
The syntax of the function is IFS(test1, if_true1, test2, if_true2,...)
where you enter the condition for each test
argument and the value to return if the test is true for each true
plot.
Let’s look at some basic examples.
Return cell reference values
Here, the condition looks for the id number in cell C2. When the formula finds the matching condition, from 1 to 5, it returns the corresponding name.
=IFS(C2=1,A2,C2=2,A3,C2=3,A4,C2=4,A5,C2=5,A6)
To break down the formula, if the value in cell C2 is 1, return the value in A2, if 2, return the value in A3, if 3, return the value in A4, if 4, return the value in A5 , and if it is 5, it returns the value in A6.
If you were to use a nested IF statement instead of the IFS function, your formula would look like this:
=IF(C2=1,A2,IF(C2=2,A3,IF(C2=3,A4,IF(C2=4, A5,IF(C2=5,A6)))))
While you get the same result using either option, the formula for the IFS function is a bit less confusing and doesn’t take as long to assemble.
RELATED: The Basics of Structuring Formulas in Microsoft Excel
return numbers
For another IFS example, we will apply bonuses to our sellers based on their sales totals. Here is the formula:
=IFS(F2>100000,1000,F2>75000,750,F2>50000,500)
To break down this formula, if the total in cell F2 is greater than 100,000, it returns 1,000, if it is greater than 75,000, it returns 750, and if it is greater than 50,000, it returns 500.
Advice: You can use the fill handle to copy the same IFS formula to adjacent cells.
To compare again, this is what the formula looks like as a nested IF statement:
=IF(F2>100000,1000,IF(F2>75000,750,IF(F2>50000,500)))
By using the formula for the IFS function, you eliminate the need to repeatedly type IF with an opening parenthesis and remember the correct number of closing parentheses at the end.
Return text values
In the example below, we have a list of employees who have not completed their training. We will display a text result based on the percentage complete in cell B2. Note that text values ​​must be enclosed in quotes.
=IFS(B2<50,"Less than half",B2=50,"Half",B2>50,"More than half")
To break down this formula, if the value in cell B2 is less than 50, return “Less than half”, if equal to 50, return “Half”, and if greater than 50, return “More than half”. “.
For further comparison, this is what the nested IF statement looks like to get the same result:
=IF(B2<50,"Less than half",IF(B2=50,"Half",IF(B2>50,"More than half")))
It can be especially difficult to fix formula errors in long statements or those that require special care, such as including quotation marks. This is just one more reason to consider using the IFS function instead of a nested IF formula.
As Microsoft explains:
Various IF statements require a great deal of thought to build correctly and to make sure your logic can correctly calculate through each condition to the end. If you don’t nest your formula with 100% accuracy, it might work 75% of the time, but return unexpected results 25% of the time.
Microsoft goes on to say that nested IF statements can be difficult to maintain. This is another consideration for using the IFS function, especially if you’re working on your spreadsheet with others.
RELATED: How to fix common formula errors in Microsoft Excel
When you want to test multiple conditions for a cell, or even a range of cells, consider using the IFS function. To learn more, learn how to use the other logical functions in Excel like AND, OR, and XOR.
[ad_2]