AND and OR functions in Google Sheets - 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 certain criteria for special promotions.

📊 Data: You have a table with sales data including columns for Salesperson, Region, Sales Amount, and Product Category.

🎯 Deliverable: Create columns that use AND and OR functions to flag transactions that qualify for two different promotion rules.

Sample Data

Salesperson	Region	Sales Amount	Product Category
Alice	East	1200	Electronics
Bob	West	800	Furniture
Charlie	East	1500	Electronics
Diana	South	700	Clothing
Eva	West	1300	Electronics
Frank	East	600	Furniture
Grace	South	1100	Electronics
Hank	West	900	Clothing

1

Step 1: Add a new column named 'Promo Rule 1' to check if the sale is in the East region AND the sales amount is greater than 1000.

=AND(B2="East", C2>1000)

Expected Result

For row 2 (Alice), result is TRUE because Region is East and Sales Amount is 1200.

2

Step 2: Copy the formula from step 1 down the 'Promo Rule 1' column for all rows.

Drag the formula down from row 2 to row 9.

Expected Result

Rows with TRUE: Alice (row 2), Charlie (row 4). Others FALSE.

3

Step 3: Add another new column named 'Promo Rule 2' to check if the sale is either in the West region OR the product category is Electronics.

=OR(B2="West", D2="Electronics")

Expected Result

For row 2 (Alice), result is TRUE because Product Category is Electronics.

4

Step 4: Copy the formula from step 3 down the 'Promo Rule 2' column for all rows.

Drag the formula down from row 2 to row 9.

Expected Result

Rows with TRUE: Alice, Bob, Charlie, Eva, Grace, Hank. Rows with FALSE: Diana, Frank.

5

Step 5: Filter the data to show only rows where 'Promo Rule 1' is TRUE to see sales qualifying for the first promotion.

Use filter on 'Promo Rule 1' column for TRUE values.

Expected Result

Filtered rows: Alice, Charlie.

6

Step 6: Filter the data to show only rows where 'Promo Rule 2' is TRUE to see sales qualifying for the second promotion.

Use filter on 'Promo Rule 2' column for TRUE values.

Expected Result

Filtered rows: Alice, Bob, Charlie, Eva, Grace, Hank.

Final Result

Salesperson | Region | Sales Amount | Product Category | Promo Rule 1 | Promo Rule 2
-------------------------------------------------------------------------------------
Alice       | East   | 1200         | Electronics      | TRUE         | TRUE
Bob         | West   | 800          | Furniture        | FALSE        | TRUE
Charlie     | East   | 1500         | Electronics      | TRUE         | TRUE
Diana       | South  | 700          | Clothing         | FALSE        | FALSE
Eva         | West   | 1300         | Electronics      | FALSE        | TRUE
Frank       | East   | 600          | Furniture        | FALSE        | FALSE
Grace       | South  | 1100         | Electronics      | FALSE        | TRUE
Hank        | West   | 900          | Clothing         | FALSE        | TRUE

Bonus Challenge

Create a third column that flags sales qualifying for both promotions at the same time using AND and OR functions.

Show Hint

Use =AND(AND(B2="East", C2>1000), OR(B2="West", D2="Electronics")) or combine the previous columns with AND.

Practice

1. What does the AND function do in Google Sheets?

easy

A. Returns TRUE only if all conditions are TRUE

B. Returns TRUE if at least one condition is TRUE

C. Returns FALSE if all conditions are TRUE

D. Returns the sum of all conditions

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 with other options

   Returns TRUE if at least one condition is TRUE describes OR, C and D are incorrect because AND does not return FALSE if all are TRUE or sum values.

3. Final Answer:

   Returns TRUE only if all conditions are TRUE -> Option A

4. Quick Check:

   AND means all TRUE = TRUE [OK]

Hint: AND needs all TRUE to return TRUE, else FALSE [OK]

Common Mistakes:

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

2. Which of these is the correct syntax for the OR function in Google Sheets?

easy

A. =OR(A1>5, B1<10)

B. =OR(A1>5; B1<10)

C. =OR{A1>5, B1<10}

D. =OR[A1>5, B1<10]

Solution

1. Step 1: Recall correct function syntax

   Google Sheets uses commas to separate arguments inside parentheses for functions like OR.

2. Step 2: Check each option

   =OR(A1>5, B1<10) uses commas and parentheses correctly. Options A, B, and D use wrong separators or brackets.

3. Final Answer:

   =OR(A1>5, B1<10) -> Option A

4. Quick Check:

   OR uses commas inside parentheses [OK]

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

Common Mistakes:

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

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

medium

A. 0

B. FALSE

C. Error

D. TRUE

Solution

1. Step 1: Evaluate inner OR function

   OR(B1>5, C1=10) means OR(3>5, 10=10) -> OR(FALSE, TRUE) -> TRUE.

2. Step 2: Evaluate AND function

   AND(A1>5, TRUE) means AND(7>5, TRUE) -> AND(TRUE, TRUE) -> TRUE.

3. Final Answer:

   TRUE -> Option D

4. Quick Check:

   AND with all TRUE returns TRUE [OK]

Hint: Evaluate OR inside AND first, then combine results [OK]

Common Mistakes:

• Evaluating AND before OR
• Confusing TRUE and FALSE values
• Assuming numeric values instead of logical

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

medium

A. Parentheses are not needed

B. OR cannot be used inside AND

C. Missing comma between conditions inside AND

D. No error, formula is correct

Solution

1. Step 1: Analyze formula structure

   The formula tries to combine conditions inside AND but misses a comma between A1>5 and OR B1<10.

2. Step 2: Correct syntax for nested functions

   AND requires each condition separated by commas. The correct form is =AND(A1>5, OR(B1<10)).

3. Final Answer:

   Missing comma between conditions inside AND -> Option C

4. Quick Check:

   Separate conditions with commas in AND [OK]

Hint: Use commas to separate all conditions inside AND [OK]

Common Mistakes:

• Omitting commas between arguments
• Thinking OR can't be nested inside AND
• Ignoring parentheses placement

5. You want to check if a student passed both Math and English (score ≥ 50) or scored above 90 in Science. Which formula correctly returns TRUE if either condition is met?

hard

A. =OR(Math>=50, English>=50, Science>90)

B. =OR(AND(Math>=50, English>=50), Science>90)

C. =AND(Math>=50, English>=50, Science>90)

D. =AND(OR(Math>=50, English>=50), Science>90)

Solution

1. Step 1: Understand conditions

   The student passes if both Math and English scores are at least 50 (AND condition) OR if Science score is above 90.

2. Step 2: Translate to formula

   Use AND(Math>=50, English>=50) for both passing, then OR with Science>90 to allow either condition.

3. Final Answer:

   =OR(AND(Math>=50, English>=50), Science>90) -> Option B

4. Quick Check:

   Use OR for either condition, AND for both subjects [OK]

Hint: Use AND inside OR to combine conditions correctly [OK]

Common Mistakes:

• Mixing AND and OR order incorrectly
• Using AND for all conditions instead of OR
• Not grouping conditions properly