Combining clauses in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Combine AND and OR clauses in a formula

What is the result of this formula if A1=5, B1=10, and C1=3?

=IF(AND(A1>3, OR(B1=10, C1=5)), "Yes", "No")

A "Yes"

B TRUE

C 0

D "No"

💡 Hint

Remember AND needs all TRUE, OR needs at least one TRUE.

A1 is greater than 3 (TRUE), and OR(B1=10, C1=5) is TRUE because B1=10. So AND(TRUE, TRUE) is TRUE, so IF returns "Yes".

❓ Function Choice

intermediate

Choose the correct formula combining clauses

Which formula returns TRUE only if A1 is greater than 10 and either B1 equals 5 or C1 is less than 3?

A =OR(AND(A1>10, B1=5), C1<3)

B =AND(A1>10, OR(B1=5, C1<3))

C =AND(OR(A1>10, B1=5), C1<3)

D =OR(A1>10, AND(B1=5, C1<3))

💡 Hint

AND means all conditions must be true; OR means one of the conditions must be true.

The condition requires A1>10 AND (B1=5 OR C1<3). Option B matches this exactly.

❓ data_analysis

advanced

Filter data with combined conditions

You have a list of sales in column A and regions in column B. You want to filter rows where sales are greater than 1000 and region is either "East" or "West". Which FILTER formula gives the correct filtered list?

A =FILTER(A2:B100, A2:A100>1000, OR(B2:B100="East", B2:B100="West"))

B =FILTER(A2:B100, A2:A100>1000, (B2:B100="East")+(B2:B100="West"))

C =FILTER(A2:B100, AND(A2:A100>1000, OR(B2:B100="East", B2:B100="West")))

D =FILTER(A2:B100, (A2:A100>1000)*( (B2:B100="East")+(B2:B100="West") ))

💡 Hint

In FILTER, use multiplication (*) for AND and addition (+) for OR with arrays.

Option D correctly uses multiplication for AND and addition for OR in array context. Option D and C use OR and AND functions which do not work with arrays in FILTER. Option D uses addition but misses the AND multiplication.

🎯 Scenario

advanced

Create a formula to assign grades based on multiple conditions

You want to assign grades in column C based on scores in column B:
- "A" if score >= 90
- "B" if score >= 80 and < 90
- "C" if score >= 70 and < 80
- "F" otherwise

Which formula in C2 correctly uses combined clauses to assign grades?

A =IF(B2>=90, "A", IF(AND(B2>=80, B2<90), "B", IF(AND(B2>=70, B2<80), "C", "F")))

B =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))

C =IF(AND(B2>=90, B2<100), "A", IF(AND(B2>=80, B2<90), "B", IF(AND(B2>=70, B2<80), "C", "F")))

D =IF(B2>90, "A", IF(B2>80, "B", IF(B2>70, "C", "F")))

💡 Hint

Use nested IFs with AND to check ranges precisely.

Option A correctly checks ranges with AND for B and C grades. Option A misses upper bounds, so 90 would get B incorrectly. Option A unnecessarily checks upper bound for A and excludes 90. Option A uses > instead of >= and misses exact boundaries.

📊 Formula Result

expert

Evaluate complex combined logical formula output

Given the values:
A1=4, B1=7, C1=10
What is the output of this formula?

=IF(OR(AND(A1>3, B1<5), AND(B1>5, C1<15)), "Pass", "Fail")

A TRUE

B "Fail"

C "Pass"

D FALSE

💡 Hint

Check each AND condition inside the OR separately.

AND(A1>3, B1<5) is FALSE because B1=7 is not less than 5. AND(B1>5, C1<15) is TRUE because 7>5 and 10<15. OR(FALSE, TRUE) is TRUE, so IF returns "Pass".

Practice

1. Which of the following best describes the AND function in Google Sheets?

easy

A. It returns FALSE if all conditions inside it are TRUE.

B. It returns TRUE if at least one condition inside it is TRUE.

C. It returns TRUE only if no conditions are provided.

D. It returns TRUE only if all conditions inside it are TRUE.

Solution

1. Step 1: Understand the AND function behavior

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

2. Step 2: Compare options with the AND function definition

   It returns TRUE only if all conditions inside it are TRUE. correctly states that AND returns TRUE only if all conditions are TRUE, while others describe OR or incorrect behavior.

3. Final Answer:

   It returns TRUE only if all conditions inside it are TRUE. -> Option D

4. Quick Check:

   AND = all TRUE conditions [OK]

Hint: AND needs all TRUE; remember 'and' means all together [OK]

Common Mistakes:

• Confusing AND with OR function
• Thinking AND returns TRUE if any condition is TRUE
• Assuming AND returns FALSE when all conditions are TRUE

2. Which of these formulas correctly uses OR to check if cell A1 is 10 or 20?

easy

A. =OR(A1=10, A1=20)

B. =OR(A1==10, A1==20)

C. =OR(A1=10; A1=20)

D. =OR(A1=10 A1=20)

Solution

1. Step 1: Check correct syntax for OR function

   OR takes conditions separated by commas, and uses single equals (=) for comparison in Google Sheets.

2. Step 2: Identify the correct formula

   =OR(A1=10, A1=20) uses commas and single equals correctly. =OR(A1==10, A1==20) uses double equals which is invalid in Sheets. =OR(A1=10; A1=20) uses semicolon which is locale dependent but usually comma is standard. =OR(A1=10 A1=20) misses separator between conditions.

3. Final Answer:

   =OR(A1=10, A1=20) -> Option A

4. Quick Check:

   OR syntax uses commas and single = [OK]

Hint: Use commas and single = inside OR for conditions [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Separating conditions with semicolons or spaces incorrectly
• Missing commas between conditions

3. What is the result of this formula if A1=5 and B1=10?
=IF(AND(A1>3, B1<15), "Yes", "No")

medium

A. Yes

B. No

C. TRUE

D. FALSE

Solution

1. Step 1: Evaluate each condition inside AND

   A1>3 is TRUE because 5 > 3. B1<15 is TRUE because 10 < 15.

2. Step 2: Determine AND result and IF output

   AND(TRUE, TRUE) returns TRUE, so IF returns "Yes" as the first argument is TRUE.

3. Final Answer:

   Yes -> Option A

4. Quick Check:

   Both conditions TRUE -> IF returns "Yes" [OK]

Hint: AND needs all TRUE to return TRUE inside IF [OK]

Common Mistakes:

• Assuming OR behavior instead of AND
• Confusing TRUE/FALSE output with IF text output
• Ignoring one condition's value

4. Identify the error in this formula:
=IF(OR(A1=5 B1=10), "Match", "No Match")

medium

A. Incorrect use of IF function

B. Missing comma between conditions inside OR

C. Using double quotes incorrectly

D. Using AND instead of OR

Solution

1. Step 1: Check syntax inside OR function

   Conditions inside OR must be separated by commas. Here, A1=5 and B1=10 are missing a comma.

2. Step 2: Confirm other parts of formula are correct

   IF syntax and quotes are correct. Using OR is valid here, so the main error is missing comma.

3. Final Answer:

   Missing comma between conditions inside OR -> Option B

4. Quick Check:

   Separate OR conditions with commas [OK]

Hint: Separate conditions with commas inside OR and AND [OK]

Common Mistakes:

• Leaving out commas between conditions
• Confusing IF syntax errors with OR syntax
• Replacing OR with AND unnecessarily

5. You want to show "Pass" if score in A1 is between 50 and 80 inclusive, otherwise "Fail". Which formula correctly combines clauses?

hard

A. =IF(OR(A1>=50, A1<=80), "Pass", "Fail")

B. =IF(AND(A1>50, A1<80), "Pass", "Fail")

C. =IF(AND(A1>=50, A1<=80), "Pass", "Fail")

D. =IF(OR(A1>50, A1<80), "Pass", "Fail")

Solution

1. Step 1: Understand the condition for score between 50 and 80 inclusive

   The score must be greater than or equal to 50 AND less than or equal to 80.

2. Step 2: Check each formula's logic

   =IF(AND(A1>=50, A1<=80), "Pass", "Fail") uses AND with >= and <= correctly. =IF(OR(A1>=50, A1<=80), "Pass", "Fail") uses OR which is wrong because it passes if either condition is true. =IF(AND(A1>50, A1<80), "Pass", "Fail") uses strict > and < which excludes 50 and 80. =IF(OR(A1>50, A1<80), "Pass", "Fail") uses OR and strict inequalities, which is incorrect.

3. Final Answer:

   =IF(AND(A1>=50, A1<=80), "Pass", "Fail") -> Option C

4. Quick Check:

   Use AND with inclusive conditions for range checks [OK]

Hint: Use AND with >= and <= for inclusive range checks [OK]

Common Mistakes:

• Using OR instead of AND for range
• Using > and < instead of >= and <=
• Confusing inclusive and exclusive conditions