Chapter 10: Advanced Conditional Logic with Nested IF, AND, OR, and IFS Functions

Introduction to Advanced Conditional Logic
Excel’s IF function becomes even more powerful when combined with other logical functions like AND, OR, and IFS. These functions allow you to handle multiple conditions simultaneously, providing greater flexibility in your calculations and data analysis. In this chapter, we’ll explore how to use nested IF functions, and how to combine AND, OR, and IFS for more complex scenarios.

Nested IF Functions

Purpose: Nested IF functions allow you to evaluate multiple conditions in a single formula. This is useful when you need to perform different actions based on multiple criteria.

Syntax:
=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false))

• logical_test1: The first condition to evaluate.
• value_if_true1: The result if the first condition is true.
• logical_test2: The second condition to evaluate if the first condition is false.
• value_if_true2: The result if the second condition is true.
• value_if_false: The result if neither condition is true.

Example:

• Scenario: Categorize students based on their scores:
  • Score ≥ 90: “Excellent”
  • Score ≥ 70 and < 90: “Good”
  • Score < 70: “Needs Improvement”
• Formula:
  =IF(A1 >= 90, “Excellent”, IF(A1 >= 70, “Good”, “Needs Improvement”))

Using AND and OR with IF

AND Function:

• Purpose: Evaluates multiple conditions and returns TRUE only if all conditions are TRUE.
• Syntax:
  =AND(condition1, condition2, …)
• Example:
  • Scenario: Check if a score is between 70 and 90:
    =AND(A1 >= 70, A1 < 90)
  • IF with AND:
    =IF(AND(A1 >= 70, A1 < 90), “Good”, “Not Good”)

OR Function:

• Purpose: Evaluates multiple conditions and returns TRUE if at least one condition is TRUE.
• Syntax:
  =OR(condition1, condition2, …)
• Example:
  • Scenario: Check if a score is either below 70 or above 90:
    =OR(A1 < 70, A1 > 90)
  • IF with OR:
    =IF(OR(A1 < 70, A1 > 90), “Out of Range”, “In Range”)

Using IFS Function

Purpose: The IFS function allows you to evaluate multiple conditions without needing to nest IF functions. It is a more streamlined approach for handling multiple criteria.

Syntax:
=IFS(condition1, value_if_true1, condition2, value_if_true2, …)

• condition1: The first condition to evaluate.
• value_if_true1: The result if the first condition is true.
• condition2: The second condition to evaluate.
• value_if_true2: The result if the second condition is true.

Example:

• Scenario: Categorize performance based on scores:
  • Score ≥ 90: “Excellent”
  • Score ≥ 70 and < 90: “Good”
  • Score < 70: “Needs Improvement”
• Formula:
  =IFS(A1 >= 90, “Excellent”, A1 >= 70, “Good”, A1 < 70, “Needs Improvement”)

Practice Assignment:

Employee Performance Evaluation

1. Set Up Your Data:
   • Create a table with employee names, sales figures, and performance evaluations.
2. Using Nested IF Functions:
   • Categorize employees based on their sales:
     • Sales ≥ 5000: “Top Performer”
     • Sales ≥ 3000 and < 5000: “Average Performer”
     • Sales < 3000: “Needs Improvement”
   • Formula:
     =IF(B2 >= 5000, “Top Performer”, IF(B2 >= 3000, “Average Performer”, “Needs Improvement”))
3. Using AND and OR Functions:
   • Check if employees qualify for a bonus based on multiple criteria:
     • Sales ≥ 3000 AND performance rating = “Good”
   • Formula:
     =IF(AND(B2 >= 3000, C2 = “Good”), “Bonus Eligible”, “Not Eligible”)
   • Check if employees do not qualify for a bonus:
     • Sales < 3000 OR performance rating ≠ “Good”
   • Formula:
     =IF(OR(B2 < 3000, C2 <> “Good”), “No Bonus”, “Bonus”)
4. Using IFS Function:
   • Evaluate performance based on sales and performance rating.
   • Formula:
     =IFS(B2 >= 5000, “Top Performer”, B2 >= 3000, “Average Performer”, B2 < 3000, “Needs Improvement”)

Pro Tip:
When working with complex conditions, use the IFS function for better readability and manageability compared to deeply nested IF functions. For large datasets, combining AND and OR with IF can help streamline data analysis and decision-making processes.

This chapter introduces advanced conditional logic using nested IF functions, AND, OR, and IFS. These tools are essential for handling complex criteria and making data-driven decisions in Excel. At Anjni Computer Education, we emphasize practical applications and real-world scenarios to help you master these powerful Excel functions effectively.