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".