Combining AND, OR, and NOT Functions in Excel

Course: Advanced Excel Training

Topic: Combining AND, OR, and NOT Functions

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Free Course Demo

Official Website: JustAcademy

1. Introduction to AND, OR, and NOT Functions

Excel provides logical functions that help users evaluate conditions and make decisions based on data. The AND, OR, and NOT functions are especially useful when a worksheet contains multiple conditions that must be checked together.

These functions can be combined with IF, IFS, SUMIFS, COUNTIFS, conditional formatting, and other Excel features to build advanced formulas for reporting, data analysis, employee evaluation, sales tracking, inventory management, and financial calculations.

For example, a company may want to identify employees who meet both attendance and performance requirements, customers who qualify through at least one of several conditions, or products that do not belong to a restricted category.

2. Learning Objectives

• Understand the purpose and syntax of AND, OR, and NOT functions.
• Evaluate multiple logical conditions in one formula.
• Combine logical functions with IF and IFS.
• Use nested logical functions for complex business rules.
• Apply logical formulas to sales, attendance, inventory, and eligibility data.
• Troubleshoot common logical formula errors.

3. Understanding the AND Function

Definition

The AND function checks multiple conditions and returns TRUE only when every specified condition is TRUE. If even one condition is FALSE, the function returns FALSE.

Syntax

=AND(logical1, [logical2], ...)

Arguments:

• logical1: The first condition that Excel evaluates.
• logical2: An optional additional condition.
• Additional conditions: You can evaluate multiple logical tests in a single AND function.

Example 1: Checking employee eligibility

Suppose an employee must have a performance score of at least 80 and attendance of at least 90% to qualify for a bonus.

Assume the performance score is in cell B2 and attendance is in C2. Use:

=AND(B2>=80,C2>=90%)

Explanation: Excel checks both conditions. Rahul qualifies because both conditions are TRUE. Priya fails the performance requirement, while Amit fails the attendance requirement.

Example 2: Combining AND with IF

=IF(AND(B2>=80,C2>=90%),"Eligible","Not Eligible")

This formula displays a readable result instead of TRUE or FALSE. It returns "Eligible" only when the performance and attendance requirements are both satisfied.

4. Understanding the OR Function

Definition

The OR function evaluates multiple conditions and returns TRUE if at least one condition is TRUE. It returns FALSE only when all the specified conditions are FALSE.

Syntax

=OR(logical1, [logical2], ...)

Example 1: Sales incentive eligibility

A salesperson qualifies for an incentive if monthly sales reach ₹100,000 or the salesperson acquires at least 20 new customers.

Assume monthly sales are in B2 and new customers are in C2.

=OR(B2>=100000,C2>=20)

If monthly sales are ₹80,000 but the salesperson acquires 25 new customers, the formula returns TRUE because one condition is satisfied.

Example 2: Combining OR with IF

=IF(OR(B2>=100000,C2>=20),"Incentive Eligible","Not Eligible")

This formula is useful when a business offers multiple ways to qualify for a reward or benefit.

5. Understanding the NOT Function

Definition

The NOT function reverses a logical result. If a condition evaluates to TRUE, NOT returns FALSE. If the condition evaluates to FALSE, NOT returns TRUE.

Syntax

=NOT(logical)

Example 1: Identifying products that are not discontinued

Suppose cell B2 contains a product status such as "Discontinued" or "Active".

=NOT(B2="Discontinued")

The formula returns TRUE when the product is not marked as discontinued and FALSE when it is discontinued.

Example 2: Combining NOT with IF

=IF(NOT(B2="Discontinued"),"Available for Sale","Do Not Sell")

This formula checks whether the product status differs from "Discontinued" and returns an appropriate message.

6. Combining AND and OR Functions

AND and OR can be nested inside one another to evaluate conditions involving groups of requirements. This is useful when a rule requires all conditions in one group but permits alternatives in another group.

Example: Customer discount eligibility

A customer qualifies for a discount if the customer spends at least ₹50,000 and is either a premium member or has placed at least 10 orders.

Assume:

• B2 contains the purchase amount.
• C2 contains the membership type.
• D2 contains the total number of orders.

=AND(B2>=50000,OR(C2="Premium",D2>=10))

How it works:

1. Excel checks whether the purchase amount is at least ₹50,000.
2. The OR function checks whether the customer is a premium member or has placed at least 10 orders.
3. The AND function requires both the purchase condition and at least one alternative condition to be satisfied.

If the customer spends ₹60,000 and has placed 12 orders, the formula returns TRUE even if the customer is not a premium member.

7. Combining AND and NOT Functions

This combination is useful when every positive requirement must be met while a specific condition must be excluded.

Example: Approving a product for sale

A product can be approved if its stock is greater than zero, its quality score is at least 80, and it is not discontinued.

Assume stock is in B2, quality score is in C2, and status is in D2.

=AND(B2>0,C2>=80,NOT(D2="Discontinued"))

The formula returns TRUE only when stock is available, quality meets the minimum score, and the product is not discontinued.

Returning a descriptive result

=IF(AND(B2>0,C2>=80,NOT(D2="Discontinued")),"Approved","Not Approved")

This approach helps prevent products with insufficient stock, low quality, or discontinued status from being approved.

8. Combining OR and NOT Functions

OR and NOT can be used together when a rule accepts at least one condition but also requires another condition to be false.

Example: Identifying customers who can receive an offer

A customer may qualify if the customer is a Gold or Platinum member, provided the account is not blocked.

Assume membership type is in B2 and account status is in C2.

=AND(OR(B2="Gold",B2="Platinum"),NOT(C2="Blocked"))

The formula returns TRUE when the customer has either membership type and the account is not blocked.

Although the formula contains OR and NOT, the outer AND is important because both the membership requirement and the account-status requirement must be satisfied.

9. Combining AND, OR, and NOT in One Formula

All three functions can be combined to build more advanced decision-making formulas. The most important step is to divide the business rule into smaller logical conditions before writing the formula.

Example: Employee promotion eligibility

An employee qualifies for promotion when:

• Performance score is at least 85.
• Attendance is at least 90%.
• The employee is either in the "Senior" or "Team Lead" category.
• The employee is not under disciplinary review.

Assume performance is in B2, attendance is in C2, job category is in D2, and disciplinary status is in E2.

=AND(B2>=85,C2>=90%,OR(D2="Senior",D2="Team Lead"),NOT(E2="Under Review"))

Formula with a readable result

=IF(AND(B2>=85,C2>=90%,OR(D2="Senior",D2="Team Lead"),NOT(E2="Under Review")),"Promotion Eligible","Not Eligible")

Explanation:

1. The first AND condition checks the performance score.
2. The second AND condition checks attendance.
3. The OR function accepts either of the two eligible job categories.
4. The NOT function excludes employees whose status is "Under Review".
5. The outer AND requires every main requirement to pass.
6. The IF function displays the final eligibility message.

10. Practical Business Examples

Example A: Student examination eligibility

A student is eligible to sit for an examination if attendance is at least 75%, fees are paid, and the student is not suspended.

Assume attendance is in B2, fee status is in C2, and disciplinary status is in D2.

=AND(B2>=75%,C2="Paid",NOT(D2="Suspended"))

Example B: Inventory reorder alert

A reorder alert should appear when stock is below 20 units or the product is marked as high demand.

Assume stock is in B2 and demand status is in C2.

=IF(OR(B2<20,C2="High"),"Reorder Required","Stock Sufficient")

Example C: Sales target assessment

A salesperson meets the target if sales are at least ₹150,000 and returns are no more than ₹10,000, while the salesperson is not marked inactive.

Assume sales are in B2, returns are in C2, and employee status is in D2.

=AND(B2>=150000,C2<=10000,NOT(D2="Inactive"))

Example D: Customer exclusion from a marketing campaign

A customer is eligible if the customer is a Gold member or has purchased at least ₹25,000, but the customer must not have opted out of marketing communications.

Assume membership is in B2, purchase amount is in C2, and opt-out status is in D2.

=AND(OR(B2="Gold",C2>=25000),NOT(D2="Yes"))

11. Comparison of AND, OR, and NOT

12. Understanding Logical Results

Excel evaluates logical tests and returns the Boolean values TRUE or FALSE. These values can be used directly or passed into other functions.

For NOT, the result is reversed:

13. Using Logical Functions with IFS

The IFS function checks multiple conditions and returns the result associated with the first TRUE condition. AND and OR can be used inside IFS to define more complex rules.

Example: Classifying employee performance

Assume the performance score is in B2 and attendance is in C2.

=IFS(AND(B2>=90,C2>=95%),"Excellent",AND(B2>=75,C2>=85%),"Good",TRUE,"Needs Improvement")

How it works:

• Excellent: performance is at least 90 and attendance is at least 95%.
• Good: performance is at least 75 and attendance is at least 85%, provided the first condition was not already satisfied.
• Needs Improvement: the fallback result when neither previous condition is TRUE.

Conditions should be ordered carefully because IFS returns the result for the first matching condition.

14. Common Mistakes and Troubleshooting

15. Best Practices for Writing Logical Formulas

• Translate the business rule into simple statements before creating the formula.
• Use parentheses to make nested conditions easy to understand.
• Keep text labels consistent, including spelling and spacing.
• Use cell references instead of hard-coded values when thresholds may change.
• Test individual AND, OR, and NOT expressions before combining them.
• Use IF or IFS to return meaningful labels instead of only TRUE or FALSE.
• Use helper columns for very complex calculations to simplify troubleshooting.
• Check boundary values such as exactly 80 points or exactly 90% attendance.
• Document unusual business rules so other spreadsheet users can maintain the formula.

16. Practice Exercises

Exercise 1: Student eligibility

A student qualifies if marks are at least 70, attendance is at least 75%, and the student is not suspended.

Task: Write a formula using AND and NOT.

Sample answer:

=AND(B2>=70,C2>=75%,NOT(D2="Suspended"))

Exercise 2: Sales incentive

An employee qualifies if sales are at least ₹100,000 or new customer acquisitions are at least 15.

Task: Write a formula using OR.

Sample answer:

=OR(B2>=100000,C2>=15)

Exercise 3: Product approval

A product is approved when stock is above zero, quality is at least 85, and the product is not discontinued.

Task: Combine AND and NOT.

Sample answer:

=AND(B2>0,C2>=85,NOT(D2="Discontinued"))

Exercise 4: Premium customer offer

A customer qualifies if the customer is a Gold member or has spent at least ₹50,000, provided the account is not blocked.

Task: Combine AND, OR, and NOT.

Sample answer:

=AND(OR(B2="Gold",C2>=50000),NOT(D2="Blocked"))

Exercise 5: Employee promotion

An employee must have a score of at least 85, attendance of at least 90%, belong to either the Senior or Team Lead category, and not be under review.

Task: Create a complete logical formula that checks all requirements.

Sample answer:

=AND(B2>=85,C2>=90%,OR(D2="Senior",D2="Team Lead"),NOT(E2="Under Review"))

17. Mini Project: Employee Eligibility Dashboard

Create an Excel worksheet that evaluates employee eligibility for bonuses and promotions.

Suggested columns

Formula for the dashboard

=IF(AND(B2>=85,C2>=90%,OR(D2="Senior",D2="Team Lead"),NOT(E2="Under Review")),"Eligible","Not Eligible")

Project steps

1. Enter sample employee records in the worksheet.
2. Apply the formula in cell F2.
3. Fill the formula down for all employees.
4. Test the formula with different scores, attendance percentages, roles, and review statuses.
5. Apply conditional formatting to highlight eligible employees.
6. Use filters to display eligible and ineligible employees separately.
7. Verify the output against the stated eligibility rules.

18. Interview Questions and Answers

Q1. What is the difference between AND and OR?

Answer: AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE.

Q2. What does the NOT function do?

Answer: NOT reverses a logical result, changing TRUE to FALSE and FALSE to TRUE.

Q3. Can AND and OR be combined in the same formula?

Answer: Yes. They can be nested to represent rules that contain mandatory requirements and alternative conditions.

Q4. Why would you combine NOT with AND?

Answer: This combination can require multiple conditions to be true while excluding a particular status, category, or situation.

Q5. How can you display a message instead of TRUE or FALSE?

Answer: Use IF with the logical expression, such as =IF(AND(A2>=50,B2="Yes"),"Pass","Fail").

Q6. Can these functions evaluate text conditions?

Answer: Yes. They can evaluate text comparisons such as A2="Approved" or B2<>"Blocked".

Q7. How do you troubleshoot a complex logical formula?

Answer: Evaluate each condition separately, confirm cell references, inspect parentheses, and test boundary values before combining the expressions.

19. Quick Revision

• AND: Every condition must be TRUE.
• OR: At least one condition must be TRUE.
• NOT: Reverses a logical result.
• AND + OR: Combines mandatory conditions with alternatives.
• AND + NOT: Checks multiple requirements while excluding a condition.
• OR + NOT: Evaluates alternative conditions while excluding a specified case, often inside AND.
• AND + OR + NOT: Builds advanced eligibility and decision-making rules.
• IF and IFS: Convert logical outcomes into readable categories or messages.

20. Conclusion

Combining AND, OR, and NOT functions is an essential Advanced Excel skill for creating reliable decision-making formulas. AND checks whether all requirements are satisfied, OR checks whether at least one alternative is satisfied, and NOT excludes or reverses a particular condition.

When combined with IF and IFS, these functions can automate employee evaluations, student eligibility, sales incentives, inventory alerts, customer offers, and other business processes. Practice by writing each condition separately first, then combine the conditions into a formula that accurately represents the required business rule.

Explore more learning opportunities through JustAcademy, review the Advanced Excel Course, or register for a course demo.