AND function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to identify which sales transactions meet two conditions: the sale amount is greater than $500 and the customer rating is 4 or higher.

📊 Data: You have a table with sales transactions including columns for Transaction ID, Sale Amount, and Customer Rating.

🎯 Deliverable: Create a new column that uses the AND function to mark TRUE if both conditions are met, otherwise FALSE.

Sample Data

Transaction ID	Sale Amount	Customer Rating
TXN001	450	5
TXN002	600	3
TXN003	700	4
TXN004	300	4
TXN005	800	5
TXN006	550	2
TXN007	400	4
TXN008	900	5

1

Step 1: Add a new column header named 'Meets Conditions' next to the 'Customer Rating' column.

Expected Result

A new column header 'Meets Conditions' appears in the table.

2

Step 2: In the first data row under 'Meets Conditions', enter the formula to check if Sale Amount is greater than 500 AND Customer Rating is 4 or higher.

=AND(B2>500, C2>=4)

Expected Result

Formula entered in cell D2.

3

Step 3: Copy the formula down the 'Meets Conditions' column for all rows.

Drag the fill handle from D2 down to D9.

Expected Result

Each row shows TRUE if both conditions are met, otherwise FALSE.

Final Result

Transaction ID | Sale Amount | Customer Rating | Meets Conditions
-------------------------------------------------------------
TXN001         | 450         | 5               | FALSE
TXN002         | 600         | 3               | FALSE
TXN003         | 700         | 4               | TRUE
TXN004         | 300         | 4               | FALSE
TXN005         | 800         | 5               | TRUE
TXN006         | 550         | 2               | FALSE
TXN007         | 400         | 4               | FALSE
TXN008         | 900         | 5               | TRUE

✓Only transactions TXN003, TXN005, and TXN008 meet both conditions.

✓Sales above $500 with customer rating 4 or higher are marked TRUE.

✓This helps quickly identify high-value, well-rated sales.

Bonus Challenge

Modify the formula to also check that the sale was made in the current year, assuming a 'Sale Date' column exists.

Show Hint

Use the AND function with an additional condition like YEAR(DATE_COLUMN)=YEAR(TODAY()).

Practice

1. What does the AND function do in Excel?

easy

A. Returns TRUE if any one condition inside it is true.

B. Returns TRUE if all conditions inside it are true.

C. Returns the sum of all conditions.

D. Returns FALSE if all conditions are true.

Solution

1. Step 1: Understand the purpose of AND

   The AND function checks multiple conditions and returns TRUE only if every condition is true.

2. Step 2: Compare options with AND behavior

   Returns TRUE if all conditions inside it are true. correctly states that AND returns TRUE only if all conditions are true, which matches the function's behavior.

3. Final Answer:

   Returns TRUE if all conditions inside it are true. -> Option B

4. Quick Check:

   AND = TRUE if all true [OK]

Hint: AND needs all TRUE conditions to return TRUE [OK]

Common Mistakes:

• Thinking AND returns TRUE if any condition is true
• Confusing AND with OR function
• Assuming AND sums values

2. Which of these is the correct syntax for the AND function in Excel?

easy

A. AND(condition1; condition2)

B. AND[condition1, condition2]

C. AND{condition1, condition2}

D. AND(condition1, condition2)

Solution

1. Step 1: Recall Excel function syntax

   Excel functions use parentheses () and separate arguments with commas (,).

2. Step 2: Check each option's syntax

   AND(condition1, condition2) uses parentheses and commas correctly. The other options use incorrect separators or brackets.

3. Final Answer:

   AND(condition1, condition2) -> Option D

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for function arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using square or curly brackets
• Omitting parentheses

3. Given the values in cells: A1=5, B1=10, C1=5, what is the result of =AND(A1=5, B1>5, C1<10)?

medium

A. 5

B. FALSE

C. TRUE

D. Error

Solution

1. Step 1: Evaluate each condition

   A1=5 is TRUE (5 equals 5), B1>5 is TRUE (10 is greater than 5), C1<10 is TRUE (5 is less than 10).

2. Step 2: Apply AND logic

   Since all three conditions are TRUE, AND returns TRUE.

3. Final Answer:

   TRUE -> Option C

4. Quick Check:

   All conditions true = TRUE [OK]

Hint: Check each condition; all must be true for AND to return TRUE [OK]

Common Mistakes:

• Assuming AND returns FALSE if one condition is true
• Confusing comparison operators
• Ignoring cell values

4. Identify the error in this formula: =AND(A1=5 B1>3)

medium

A. Missing comma between conditions

B. Wrong comparison operator

C. Extra parentheses

D. AND function cannot compare numbers

Solution

1. Step 1: Review formula syntax

   The AND function requires commas to separate multiple conditions inside the parentheses.

2. Step 2: Identify the missing comma

   The formula lacks a comma between A1=5 and B1>3, causing a syntax error.

3. Final Answer:

   Missing comma between conditions -> Option A

4. Quick Check:

   Separate conditions with commas in AND [OK]

Hint: Separate conditions with commas inside AND [OK]

Common Mistakes:

• Forgetting commas between conditions
• Using semicolons instead of commas
• Misplacing parentheses

5. You want to check if a student passed both Math and English. Passing score is 50. Math score is in A2, English in B2. Which formula correctly returns TRUE only if both scores are 50 or more?

hard

A. =AND(A2>=50, B2>=50)

B. =AND(A2>50, B2>50)

C. =OR(A2>=50, B2>=50)

D. =AND(A2>50 B2>50)

Solution

1. Step 1: Understand the passing condition

   Passing means scores are 50 or more, so use >=50 for both subjects.

2. Step 2: Choose correct AND formula

   =AND(A2>=50, B2>=50) uses AND with >=50 for both A2 and B2, correctly checking both conditions. =AND(A2>50, B2>50) uses >50 which excludes 50 exactly. =OR(A2>=50, B2>=50) uses OR, which returns TRUE if only one subject passes. =AND(A2>50 B2>50) misses a comma, causing syntax error.

3. Final Answer:

   =AND(A2>=50, B2>=50) -> Option A

4. Quick Check:

   Use AND with >= for both scores [OK]

Hint: Use >= and commas inside AND for both conditions [OK]

Common Mistakes:

• Using > instead of >= excludes exact passing score
• Using OR instead of AND
• Missing commas between conditions