Home
/
Excel Functions
/
IFS Function

IFS Function

The IFS function compares multiple conditions and returns a result that corresponds to the condition which gives the first TRUE as the result. The IFS function can be used when you want a single formula to test several conditions at the same time without using IF statements. It is also easier to write formulas based on the IFS function.

The IFS function can compare 127 test/value pairs. Each comparison represents a logical test that returns TRUE or FALSE, and the value that follows the condition will be returned when the logical test of the condition returns TRUE. When more than one condition returns TRUE, the value corresponding to the first TRUE logical test is returned. The order in which conditions are shown is very important to consider while using the IFS function.

Structure of IFS Function

An IFS formula with 2 logical tests can be visualized like this:

=IFS(
test1,value1 // pair 1
test2,value2 // pair 2
)

A value is returned by the IFS function only when the corresponding test returns TRUE and the first test that returns TRUE will show the output. It is better to add line breaks in the IFS formula for better readability as shown above.

Here are five examples of the IFS function in Excel, along with their syntax

Categorizing Exam Scores:

You want to categorize exam scores into grades based on specific ranges.

Syntax:

=IFS(A2 >= 90, "A", A2 >= 80, "B", A2 >= 70, "C", A2 >= 60, "D", TRUE, "F")

Pricing Based on Quantity:

You need to calculate the price based on the number of items ordered.

Syntax:

=IFS(B2 <= 10, B2 * 5, B2 <= 20, B2 * 4.5, TRUE, B2 * 4)

Assigning Performance Ratings:

You're assigning performance ratings based on employee sales.

Syntax:

=IFS(C2 >= 10000, "Excellent", C2 >= 8000, "Good", C2 >= 6000, "Satisfactory", TRUE, "Needs Improvement")

Categorizing Age Groups:

You're categorizing people into different age groups.

Syntax:

=IFS(D2 < 18, "Child", D2 < 35, "Young Adult", D2 < 60, "Adult", TRUE, "Senior")

Calculating Bonus Percentages:

You're calculating different bonus percentages based on sales performance.

Syntax:

=IFS(E2 < 5000, 5%, E2 < 10000, 10%, E2 < 15000, 15%, TRUE, 20%)

Here are five tips to help you make the most of the IFS function in Excel:

Understand the Logic: The IFS function allows you to evaluate multiple conditions and return a value based on the first true condition. It's essential to understand that only the value corresponding to the first true condition will be returned. If none of the conditions are true, you can provide a default value as the last argument.
Avoid Overlapping Conditions: When using the IFS function, ensure that your conditions don't overlap. In other words, make sure that only one condition can be true at a time to prevent unexpected results. Overlapping conditions can lead to ambiguous outcomes.
Use in Place of Nested IF Statements: The IFS function is a powerful alternative to nested IF statements when dealing with multiple conditions. Instead of creating a complex series of nested IFs, you can simplify your formulas using IFS, making them easier to read and manage.
Handle Numeric Ranges: The IFS function can also be used to handle numeric ranges. Instead of using exact comparisons, you can use conditions like value > X and value <= Y to categorize values into specific ranges, which can be very useful for data analysis.
Utilize Error Handling: If none of the conditions in your IFS function are true, you can include an optional final argument to handle this scenario. This is particularly useful for preventing #N/A errors when no conditions are met. Use this argument to provide a default value or an error message.

Common Mistakes to Avoid

Here are some common mistakes to avoid when using the IFS function in Excel, along with the correct syntax for each scenario:

Overlapping Conditions: Mistake: Using conditions that overlap, leading to ambiguous results. Correct Syntax: =IFS(A2 > 90, "A", A2 > 80, "B", A2 > 70, "C", TRUE, "D")
Missing Default Value: Mistake: Not including a default value for cases where no conditions are met. Correct Syntax: =IFS(B2 >= 10, "High", B2 >= 5, "Medium", TRUE, "Low")
Incorrect Order of Conditions: Mistake: Placing conditions in the wrong order, leading to incorrect outcomes. Correct Syntax: =IFS(C2 >= 8000, "Good", C2 >= 10000, "Excellent", TRUE, "Needs Improvement")
Misusing Logical Operators: Mistake: Using incorrect logical operators (e.g., using “>” instead of “>=”) in conditions. Correct Syntax: =IFS(D2 < 18, "Child", D2 < 35, "Young Adult", D2 < 60, "Adult", TRUE, "Senior")
Not Handling Text Properly: Mistake: Not handling text conditions with appropriate formatting or quotation marks. Correct Syntax: =IFS(E2 = "Apples", "Fruit", E2 = "Bananas", "Fruit", TRUE, "Unknown")

By avoiding these common mistakes and using the correct syntax, you can ensure that your IFS function formulas work as intended and provide accurate results based on your conditions.

Help others find us!

Md. Saiful Islam

Mr. Saiful Islam is a professional trainer of Excel and other office applications. In addition to conducting training, he has over 15 years of experience in Project Finance, Investment Management, Treasury Management, Budgetary Control, and other key areas of Finance & Accounts. He often writes about Careers, Productivity Tips, Businesses, Technologies, Banking, Investments, and more. He is available to conduct in-house training for any organization and can be reached at +8801673844325 or via his LinkedIn Profile.