The IFS function in Excel stands out as a powerful tool for managing complex conditional logic with elegance and ease. Imagine you are a skilled traffic controller at a busy intersection, directing vehicles (conditions) smoothly to their correct paths (results). Just as a traffic controller ensures each vehicle follows the appropriate route without causing chaos, the IFS function ensures each condition in your data is evaluated and directed to the correct output. This ensures that your data flows logically and accurately, without the need for convoluted nested IF statements.
The IFS function is invaluable in a variety of real-life scenarios. For instance, in customer service, it can prioritise support tickets based on urgency scores, ensuring that the most critical issues are addressed first. In education, it can categorise student grades into performance bands, allowing for quick and easy identification of top performers and those needing additional support. In business, it can classify financial transactions, streamline reporting processes, and assist in decision-making by providing clear categorizations based on multiple criteria. In this comprehensive guide, we’ll dive deep into the IFS function, uncovering its nuances, showcasing real-world examples, and exploring its limitations and solutions. Here’s our practice sheet to follow along: IFS function in excel.xlsx
Introduction to the IFS Function in Excel
The IFS function is a relatively new addition to Excel, introduced in Excel 2016. It simplifies complex nested IF statements by allowing you to evaluate multiple conditions in a single formula. This function is particularly useful when you have multiple criteria that need to be checked sequentially.
Syntax:
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
At its core, the IFS function evaluates conditions one by one and returns a corresponding value for the first true condition. If none of the conditions are true, it returns an error. Adding a default TRUE condition to check for any other condition will bypass this error.
**
Why Use IFS Instead of IF?
While the traditional IF function in Excel is a powerful tool for conditional logic, it has several limitations that can hinder efficiency and clarity, especially when dealing with multiple conditions. In contrast, the IFS function addresses these shortcomings, making it a more robust solution for complex decision-making scenarios. Below, we’ll explore the fallacies of using IF and demonstrate how IFS can simplify and enhance the process.
Fallacies of Using IF
- Nested Complexity:
- When you have to evaluate multiple conditions, using IF often leads to nested formulas, making them hard to read and maintain. For example, if you want to classify scores into grades (A, B, C, D, and F), a nested IF formula becomes convoluted and difficult to manage.
- Error-Prone:
- Nested IF statements can easily introduce errors, particularly when conditions overlap or when new conditions need to be added. Adjusting one condition can inadvertently affect others, leading to incorrect outputs.
- Limited Readability:
- As the complexity of the nested IF statements grows, the readability of the formula decreases significantly, making it challenging for users to understand the logic without extensive comments or documentation.
Example Scenario: Grading System
Let’s consider a grading system where student scores need to be categorised into grades: A, B, C, D, and F. We will use both IF and IFS to demonstrate the differences.
Data:
Table7
Using Nested IF
The formula to categorise the scores using nested IF would look like this:
=IF(Table7[Score] >= 90, “A”, IF(Table7[Score] >= 80, “B”, IF(Table7[Score] >= 70, “C”, IF(Table7[Score] >= 60, “D”, “F”))))
This formula checks each condition in a nested manner, starting from the highest grade.
Using IFS Function in Excel
In contrast, using the IFS function simplifies the grading logic:
=IFS( B2 >= 90, “A”, B2 >= 80, “B”, B2 >= 70, “C”, B2 >= 60, “D”, B2 < 60, “F”)
Alternatively, you could also use the below formula to include a default “TRUE” condition in case every other condition is unsatisfied.
=IFS( B2 >= 90, “A”, B2 >= 80, “B”, B2 >= 70, “C”, B2 >= 60, “D”, TRUE, “F”)
Advantages of IFS Over IF
- Simplicity: The IFS function allows you to define multiple conditions without the need for nested statements, making it easier to read and understand.
- Error Reduction: With IFS, each condition is independent, reducing the chances of logical errors when adding or removing conditions.
- Enhanced Readability: The structure of the IFS function clearly indicates which conditions lead to which results, making it easier for users to follow the logic.
**
Real-World Examples of IFS Function in Excel
Let’s delve into some real-world applications of the IFS function to see how it can simplify complex decision-making processes.
Example 1. Healthcare: Patient Severity Classification
Imagine you are a healthcare administrator tasked with categorising patients based on their severity scores. You have a dynamic table with patient IDs and their severity scores amongst other important columns, and you want to categorise each patient as “Critical,” “High,” “Moderate,” or “Low” based on the health severity score.
Sample of HealthCare Data from Table3
This dynamic table is called Table3.
Formula:
=IFS(Table3[Severity Score] >= 90, “Critical”,
Table3[Severity Score] >= 70, “High”,
Table3[Severity Score] >= 50, “Moderate”,
Table3[Severity Score] < 50, “Low”)
Result:
Conditions and Results:
- Condition 1: Table3[Severity Score] >= 90 → If the severity score is 90 or higher, it returns “Critical”.
- Condition 2: Table3[Severity Score] >= 70 → If the score is 70 or higher (but less than 90, since the first condition was already checked), it returns “High”.
- Condition 3: Table3[Severity Score] >= 50 → If the score is 50 or higher (but less than 70), it returns “Moderate”.
- Condition 4: Table3[Severity Score] < 50 → If the score is less than 50, it returns “Low”.
Now, let’s say based on these severity labels an observation assistant wants to see which patients require immediate medical attention and which ones do not – Anyone with Critical or High tag requires immediate medical attention whereas any other tag is considered stable. To achieve this, we can use the IFS function like below:
=IFS(
OR(L6 = “Critical”, L6 = “High”), “Needs Immediate Medical Attention”,
TRUE, “Stable Condition”
)
As you can see here, “Critical” and “High” severity patients are assigned “Needs Immediate Medical Attention” whereas the ones with “Moderate” or “Low” are assigned “Stable Condition”. Here, we use TRUE as the final logical test to ensure that a value is returned if all other conditions are FALSE.
How the TRUE Condition Works:
- Evaluation Sequence: The IFS function evaluates each logical test sequentially from left to right. As soon as a test returns TRUE, the corresponding value is returned, and the evaluation stops.
- First Match Priority: Only the first condition that evaluates as TRUE is returned. Even if subsequent conditions are also TRUE, they are not evaluated once a match is found.
- No Default Return: Unlike the IF function, IFS does not have a built-in “else” argument for when none of the conditions are met. If none of the conditions evaluate as TRUE, the formula will return an #N/A error. To handle this, you can add a final TRUE condition as a catch-all.
**
Example 2. Event Management: Organizer Level Classification (With Multiple Conditions)
Imagine you are part of an event management team responsible for assessing the success of various events throughout the year. Each event is evaluated based on attendee satisfaction, represented by post-event survey scores, and budget adherence, reflected in the overall expenditure. Using an IFS formula in Excel along with AND and OR conditions, you categorise each event as “Excellent,” “Good,” “Average,” or “Needs Improvement” based on predefined thresholds.
Before we look at IFS function in this example, let’s look at a brief overview of how AND and OR function look like:
OR Function
Purpose: The OR function checks if at least one of the specified conditions is true. It returns TRUE if any condition is met; otherwise, it returns FALSE.
OR(condition1, condition2, …)
- =OR(A1 > 10, B1 < 5) – Returns TRUE if either A1 is greater than 10 or B1 is less than 5.
AND Function
Purpose: The AND function checks if all specified conditions are true. It returns TRUE if all conditions are met; otherwise, it returns FALSE.
AND(condition1, condition2, …)
- =AND(A1 > 10, B1 < 5) – Returns TRUE only if A1 is greater than 10 and B1 is less than 5.
Now that we know what is expected of AND and OR, let’s see how we can generate a competency level for the organizers in your event management database based on the attendance and overall feedback scores they’ve got for the events conducted by them.
Here’s a snippet of the actual dataset:
Formula:
=IFS(
AND(I6 > 4.5, G6 > 500), “Excellent”,
OR(I6 > 4, G6 > 300), “Good”,
AND(I6 >= 3.5, I6 <= 4), “Average”,
TRUE, “Needs Improvement”
)
Breakdown of the Formula:
- AND(B2 > 90, C2 > 4.5): Checks if both the Completion Rate is above 90% and the Feedback Score is above 4.5. If true, it returns “Excellent”.
- OR(B2 > 80, C2 > 4.0): Checks if either the Completion Rate is above 80% or the Feedback Score is above 4.0. If true, it returns “Good”.
- AND(B2 >= 70, B2 <= 80): Checks if the Completion Rate is between 70% and 80%, inclusive. If true, it returns “Average”.
- TRUE: This is a catch-all condition for any other case, returning “Needs Improvement”.
Result:
This example illustrates how you can use the IFS function in combination with AND and OR to categorise data based on multiple conditions.
**
Using IFS with Other Functions
The true power of the IFS function in Excel is revealed when it is combined with other functions, enhancing its versatility and making it a robust tool for managing complex data scenarios. By integrating IFS with functions like VLOOKUP, AND, OR, or even SUM, you can create dynamic formulas that respond to multiple conditions, allowing for more nuanced data analysis. Here are some expanded examples showcasing how these combinations can be effectively utilised in various contexts:
Example 1. IFS with VLOOKUP for Enhanced Data Lookup
When working with large datasets, you often need to categorise data and fetch additional information based on specific criteria.
Customer Support Ticket Management
Imagine you are part of a small customer support team that needs to effectively manage and prioritise incoming support tickets based on their urgency. You have a dynamic table of tickets with urgency scores, and you want to categorise each ticket into priority levels: Urgent, High Priority, Medium Priority, and Low Priority. Additionally, you have a second table that specifies the contact person (and their email addresses) for each priority level, ensuring that tickets are assigned appropriately based on urgency. For the ease of understanding, the two functions are being implemented separately for now. However, at the end of the section, you will see how the IFS can be nested within VLOOKUP for the same result.
Ticket Table:
Contact Table:
Step 1: IFS Formula for Ticket Priority
You can use the IFS function in Excel to label each ticket’s priority based on its urgency score. In column D (for example), you can enter the following formula in some cell and drag it down:
=IFS(C6 >= 85, “Urgent”, C6 >= 65, “High Priority”, C6 >= 40, “Medium Priority”, C6 < 40, “Low Priority”)
Result:
Step 2: VLOOKUP to Assign Contact Emails
Next, use the VLOOKUP function to find the appropriate contact person email based on the priority assigned in Customer_Support_Tickets sheet (the first sheet). In column E of this sheet, you can enter the following formula and hit Ctrl+Shift+Enter to apply to other rows:
=VLOOKUP(D6, Ticket_Assignment_Contact!$B$5:$C$8, 2, FALSE)
Result:
Now, let’s see how the formula looks when one function is nested within the other.
=VLOOKUP(IFS(C16 >= 85, “Urgent”, C16 >= 65, “High Priority”, C16 >= 40, “Medium Priority”, TRUE, “Low Priority”), Ticket_Assignment_Contact!$B$5:$C$8, 2, FALSE)
As you can see, with the nested formula we could reduce the data redundancy by eliminating a column.
One may ask, why can’t we just use IFS again to find the associated Email IDs? Let’s look at why the below formula would not be feasible in complex cases.
=IFS(
B2 >= 85, “[email protected]”,
B2 >= 65, “[email protected]”,
B2 >= 40, “[email protected]”,
TRUE, “[email protected]”
)
The IFS function, while useful, has several limitations. Scalability is a major issue, as handling numerous conditions becomes cumbersome and error-prone, with hardcoded values like email addresses making updates inefficient. Memory usage is also a concern; nested IFS formulas consume more memory and processing power, with redundancy across cells exacerbating the issue. Speed suffers as well, with increased processing time for more conditions slowing down performance, particularly in large datasets like this or with frequent recalculations. Lastly, maintainability is challenging; nested IFS formulas are error-prone and inflexible, requiring careful management and updates across multiple locations whenever changes occur. Additionally, in the above approach, if the email addresses ever change, the corresponding formula will also have to be modified to accommodate the changes, else there could be data inconsistency.
**
Example 2. Variations of IFS for Mathematical Operations with Multiple Conditions – SUMIFS and AVERAGEIFS
A table in an offline shopping chain’s database includes various aspects of sales performance over four quarters, such as product names, units sold, revenue, regions, salespersons, target achievement percentages, and customer satisfaction scores. We want to calculate the total and average sales for each quarter for presenting to the stakeholders. Here’s how you can do that:
Data:
Sample Quarterly Sales Data
On the same worksheet, there is a separate table created for fetching total and average sales for each quarter in a gist.
SUMIFS:
SUMIFS (a variation of SUMIF) is a powerful function in Excel used to sum values based on multiple criteria. It allows you to specify a range to sum and apply one or more conditions to determine which cells in that range should be included in the total. The syntax for SUMIFS is:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- sum_range: The range of cells containing the values to be summed.
- criteria_range1: The first range to evaluate with the condition.
- criteria1: The condition to apply to the first range (e.g., “Q1” for summing only first-quarter data).
- [criteria_range2, criteria2]: Optional additional pairs for more conditions.
SUMIFS can be used effectively in business scenarios like sales analysis where you need to sum revenue or units sold for specific periods, products, or regions. For example, in our offline shopping chain’s database, SUMIFS can calculate total sales for each quarter by summing only the values that match specific quarters in the criteria range. This makes it an essential tool for summarizing data in complex datasets with multiple filtering requirements.
Formula:
In cell M6, this formula is written and dragged down.
=SUMIFS(Table1[Sales (Units)],Table1[Quarter],L6:L9)
Result:
AVERAGEIFS:
AVERAGEIFS is a versatile function in Excel designed to calculate the average of a range based on multiple conditions. This function is particularly useful when you want to find the mean of values that meet specific criteria. The syntax for AVERAGEIFS is:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- average_range: The range of cells containing the values you want to average.
- criteria_range1: The first range to evaluate against a condition.
- criteria1: The condition to apply to the first range (e.g., “Q1” to average values for the first quarter).
- [criteria_range2, criteria2]: Optional additional pairs for more complex conditions.
AVERAGEIFS helps filter data to only include values that meet all specified conditions, ensuring that the average reflects only relevant entries. For instance, in the offline shopping chain’s database, you can use AVERAGEIFS to find the average sales revenue for each quarter by averaging only those sales that fall under specific criteria like quarter labels or regions. This makes AVERAGEIFS an essential tool for providing targeted insights when presenting data summaries to stakeholders.
Formula:
In cell N6, this formula is written and dragged down.
=AVERAGEIFS(Table1[Sales (Units)],Table1[Quarter],L6:L9)
Result:
Here’s the chart created by following the below steps for the generated result:
- Convert the resultant data into a table by Insert > Table > My Table has headers > OK.
- Select the table and Insert > Charts > Column Chart.
**
Using ChatGPT to Enhance Your IFS Function in Excel Usage
Leveraging AI tools like ChatGPT can help you craft complex IFS formulas more efficiently. Here’s how you can use ChatGPT to assist with your Excel tasks:
Formulating Complex Logic: If you need help creating a complex IFS formula, you can describe your requirements to ChatGPT, and it can generate the formula for you.
For instance,
Prompt: “I need an IFS formula to categorize review scores (starting from cell B2) into ‘Outstanding,’ ‘Exceeds Expectations,’ ‘Meets Expectations,’ and ‘Needs Improvement’ based on the following criteria: >= 90, >= 80, >= 70, < 70.“
=IFS(B2 >= 90, “Outstanding”, B2 >= 80, “Exceeds Expectations”, B2 >= 70, “Meets Expectations”, B2 < 70, “Needs Improvement”)
Debugging Formulas: If your IFS formula isn’t working as expected, you can ask ChatGPT to review and debug it. Provide the formula and describe the issue, and ChatGPT can suggest corrections.
Prompt: “I’m trying to use an IFS formula to categorize employee performance ratings based on their scores. However, I think I made a mistake, and the formula isn’t working as expected. I’m getting an error if a value doesn’t meet any of the criteria. Here’s the formula I have:
=IFS(A2 >= 90, “Excellent”, A2 >= 75, “Good”, A2 >= 50, “Average”, A2 < 50, “Poor”)
Can you help me correct it so that it doesn’t return an error for values that don’t meet any of the criteria?”
=IFS(A2 >= 90, “Excellent”, A2 >= 75, “Good”, A2 >= 50, “Average”, A2 < 50, “Poor”, TRUE, “Invalid Score”)
Learning and Exploration: Use ChatGPT to learn more about the IFS function and explore advanced use cases. Ask questions like, “How can I use the IFS function with other Excel functions?” or “What are some advanced applications of the IFS function?”
**
Limitations of the IFS Function and Possible Solutions
Error Handling
The IFS function does not have a built-in way to handle cases where none of the conditions are met. This can result in errors.
Solution: Use the IFERROR function in conjunction with IFS to handle errors gracefully.
Performance Issues
For very large datasets, the IFS function can become slow, especially if used extensively.
Solution: Consider using helper columns to break down complex logic into simpler steps, or use other functions like SWITCH where applicable.
Limited to Excel 2016 and Later
The IFS function is only available in Excel 2016 and later versions. Users of earlier versions need to rely on nested IF statements.
Solution: For compatibility with earlier versions, use nested IF statements or consider upgrading to a newer version of Excel.
Conclusion
The IFS function in Excel is a versatile tool that simplifies complex conditional logic, making your spreadsheets more efficient and easier to manage. By leveraging real-world examples and understanding its limitations, you can unlock the full potential of the IFS function. Additionally, integrating AI tools like ChatGPT can further enhance your Excel proficiency, allowing you to create, debug, and explore complex formulas with ease.
Whether you are an HR manager evaluating employee performance, a financial analyst assessing risk, or a supply chain professional managing inventory, the IFS function can streamline your workflow and improve your data analysis capabilities. Embrace the power of the IFS function and transform the way you work with Excel. Start your journey today with this practice sheet at your dispense: IFS function in excel.xlsx
