Cross-column conditional rules in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Conditional Formatting Formula Output

You apply this conditional formatting formula in cell C2:
=AND(A2>10, B2<5)
Given A2=12 and B2=3, what is the formula result in C2?

A 12

B TRUE

C FALSE

D Error

💡 Hint

AND returns TRUE only if all conditions are true.

Since A2 is 12 (greater than 10) and B2 is 3 (less than 5), both conditions are true, so AND returns TRUE.

❓ Function Choice

intermediate

Choose the Correct Formula for Cross-Column Check

You want to mark rows where the value in column A is greater than column B. Which formula should you use in column C?

A =IF(A2>B2, "Yes", "No")

B =IF(AND(A2, B2), "Yes", "No")

C =IF(A2=B2, "Yes", "No")

D =IF(A2<B2, "Yes", "No")

💡 Hint

You want to check if A2 is greater than B2.

Option A correctly uses IF to compare A2 and B2 and returns "Yes" if A2 is greater.

🎯 Scenario

advanced

Highlight Rows Where Sales Exceed Targets

You have sales data in column A and targets in column B. You want to highlight the entire row if sales exceed targets. Which conditional formatting custom formula should you use starting from row 2?

A =$A$2>$B$2

B =AND($A2>$B2, ROW()>1)

C =A2>B2

D =$A2>$B2

💡 Hint

Use absolute column references but relative row references.

Option D uses absolute columns and relative rows, so it adjusts for each row but compares columns A and B correctly.

📊 Formula Result

advanced

Formula Result with Mixed References

In cell D2, you enter:
=IF($A2>$B$1, "Pass", "Fail")
If A2=15 and B1=10, what is the result in D2?

A Pass

B Fail

C 15

D Error

💡 Hint

Check the values and the comparison carefully.

Since $A2 is 15 and $B$1 is 10, 15 > 10 is TRUE, so IF returns "Pass".

❓ data_analysis

expert

Count Rows Where One Column is Greater Than Another

You have data in columns A and B from rows 2 to 100. Which formula correctly counts how many rows have A>B?

A =COUNTIFS(A2:A100, ">"&B2:B100)

B =COUNTIF(A2:A100, ">"&B2:B100)

C =SUMPRODUCT(--(A2:A100>B2:B100))

D =SUM(IF(A2:A100>B2:B100, 1, 0))

💡 Hint

COUNTIF and COUNTIFS do not support array comparisons like this.

SUMPRODUCT with a logical test coerced to numbers counts rows where A is greater than B correctly.