Advanced Excel Functions Guide
Advanced IF Functions with AND, OR, NOT
Logical operators (AND, OR, NOT) enhance the IF function by enabling it to handle multiple conditions.
Below are advanced examples with detailed tables.
1. AND Operator Example: Validate Score Ranges
Scenario: Determine if a student's score falls within the 'Good' range (between 70 and 90).
Formula: =IF(AND(B2>=70, B2<=90), "Good", "Needs Improvement")
Name Score Result
Alice 85 Good
Bob 92 Needs Improvement
Carol 78 Good
Dave 65 Needs Improvement
2. OR Operator Example: Identify Exceptional Cases
Scenario: Flag students who scored below 70 or above 90 as 'Exceptional'.
Formula: =IF(OR(B2<70, B2>90), "Exceptional", "Normal")
Name Score Result
Alice 85 Normal
Bob 92 Exceptional
Carol 78 Normal
Dave 65 Exceptional
3. NOT Operator Example: Exclude Certain Conditions
Scenario: Identify students who did not pass, where passing is defined as scoring at least 70.
Advanced Excel Functions Guide
Formula: =IF(NOT(B2>=70), "Failed", "Passed")
Name Score Result
Alice 85 Passed
Bob 92 Passed
Carol 78 Passed
Dave 65 Failed
4. Nested IF Example: Grade Classification
Scenario: Assign grades based on scores: A: 90 and above, B: 80 to 89, C: 70 to 79, F: Below 70.
Formula: =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))
Name Score Grade
Alice 85 B
Bob 92 A
Carol 78 C
Dave 65 F
5. Complex Logical Example: Multi-Condition Check
Scenario: Check if a student passed (score >= 70 and submitted an assignment).
Formula: =IF(AND(B2>=70, C2="Yes"), "Passed", "Failed")
Name Score Assignment Submitted? Result
Alice 85 Yes Passed
Bob 92 No Failed
Carol 78 Yes Passed
Dave 65 Yes Failed
