How to make an Excel nested if, else, and statement

To create a nested IF, ELSE, and AND statement in Excel, you use the IF function with logical conditions like AND. Here’s how to construct and use it step-by-step:

Basic Syntax

=IF(AND(condition1, condition2), value_if_true, value_if_false)

• condition1 and condition2: Logical conditions to evaluate (e.g., A1 > 10, B1 < 20).
• value_if_true: The result if the conditions are true.
• value_if_false: The result if the conditions are false (can be another IF statement).

Example 1: Simple Nested IF with AND

Suppose:

• Column A contains ages.
• You want to assign a category:
  • “Child” if the age is less than 13.
  • “Teen” if the age is between 13 and 19.
  • “Adult” if the age is 20 or more.

Formula:

=IF(A1<13, "Child", IF(AND(A1>=13, A1<=19), "Teen", "Adult"))

Explanation:

1. Check if A1 is less than 13 → If TRUE, return “Child”.
2. Otherwise, check if A1 is between 13 and 19 (AND(A1>=13, A1<=19)) → If TRUE, return “Teen”.
3. If neither condition is met, return “Adult”.

Example 2: Adding an ELSE-Like Case

Suppose:

• Column B contains grades.
• You want to assign:
  • “Pass” if the grade is 50 or more.
  • “Distinction” if the grade is 85 or more.
  • Otherwise, “Fail”.

Formula:

=IF(B1>=85, "Distinction", IF(B1>=50, "Pass", "Fail"))

Explanation:

1. If B1 >= 85, return “Distinction”.
2. If not, check if B1 >= 50 → If TRUE, return “Pass”.
3. If neither condition is true, return “Fail”.

Example 3: Combining IF with Multiple AND Conditions

Suppose:

• Column C contains salaries.
• You want to assign:
  • “Low” if the salary is less than $30,000.
  • “Middle” if the salary is between $30,000 and $70,000.
  • “High” if the salary is over $70,000.

Formula:

=IF(C1<30000, "Low", IF(AND(C1>=30000, C1<=70000), "Middle", "High"))

Explanation:

1. If C1 < 30,000, return “Low”.
2. If C1 is between $30,000 and $70,000 (AND(C1>=30000, C1<=70000)), return “Middle”.
3. If neither condition is true, return “High”.

Best Practices for Nested IF Statements

1. Keep it Simple:
   • Avoid overly complicated nested statements. They can become hard to read and debug.
2. Use Helper Columns:
   • Break down complex conditions into helper columns and reference them in the IF formula.
3. Consider Using IFS (Excel 2016 and Later):
   • For better readability, use the IFS function for multiple conditions:

     =IFS(
       C1<30000, "Low",
       AND(C1>=30000, C1<=70000), "Middle",
       C1>70000, "High"
     )
     

4. Test Conditions Individually:
   • Check each logical condition separately before combining them.

Bonus: Using OR with IF

You can combine OR with IF for alternative conditions:

=IF(OR(A1<0, A1>100), "Out of range", "In range")

• If A1 is less than 0 or greater than 100, return “Out of range”.
• Otherwise, return “In range”.

Let me know if you’d like more examples or specific help with your Excel task!