Custom formula-based rules in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Highlight cells with values greater than the average

You want to create a custom formula-based rule in Google Sheets to highlight cells in column A that have values greater than the average of all values in column A. Which formula should you use in the conditional formatting rule?

A =A1>MAX(A:A)

B =A1>SUM(A:A)/COUNT(A:A)

C =A1>AVERAGE(A:A)

D =A1>MEDIAN(A:A)

💡 Hint

Think about how to compare each cell to the average of the entire column.

The formula =A1>AVERAGE(A:A) correctly compares each cell in column A to the average of all values in column A. This formula is used in conditional formatting to highlight cells greater than the average.

Option C calculates the average manually but is equivalent to AVERAGE, so it would also work logically but is less direct. However, in Google Sheets conditional formatting, AVERAGE is preferred for clarity.

Options A and C compare to max and median, which are different statistics.

❓ Function Choice

intermediate

Choose the correct formula to highlight weekends

You want to highlight dates in column B that fall on weekends (Saturday or Sunday) using a custom formula in conditional formatting. Which formula will correctly identify weekend dates?

A =WEEKDAY(B1)=0

B =OR(WEEKDAY(B1)=7, WEEKDAY(B1)=1)

C =IF(WEEKDAY(B1)=6, TRUE, FALSE)

D =WEEKDAY(B1, 2)>5

💡 Hint

Remember that WEEKDAY can use different numbering systems for days.

Option D uses WEEKDAY(B1, 2) which numbers Monday as 1 and Sunday as 7. Days greater than 5 are Saturday (6) and Sunday (7), so this formula correctly identifies weekends.

Option D uses default WEEKDAY numbering (Sunday=1, Saturday=7), and would work, but A is more concise.

Option D only checks for Friday (6) incorrectly and Option D is invalid because WEEKDAY never returns 0.

🎯 Scenario

advanced

Create a rule to highlight duplicate entries ignoring case

You want to highlight duplicate text entries in column C regardless of letter case (e.g., "Apple" and "apple" should be considered duplicates). Which custom formula will correctly highlight duplicates ignoring case?

A =COUNTIF(ARRAYFORMULA(LOWER(C:C)), LOWER(C1))>1

B =COUNTIF(C:C, C1)>1

C =COUNTIF(C:C, LOWER(C1))>1

D =COUNTIF(C:C, EXACT(C1, C:C))>1

💡 Hint

Think about how to compare all entries in lowercase to ignore case.

Option A converts the entire column C to lowercase using ARRAYFORMULA(LOWER(C:C)) and compares it to the lowercase of the current cell. This way, duplicates ignoring case are counted correctly.

Option A is case sensitive, so it misses duplicates with different cases.

Option A compares lowercase of current cell to original column, which is case sensitive.

Option A uses EXACT which is case sensitive and returns an array, but COUNTIF does not work with arrays in that way.

❓ data_analysis

advanced

Identify cells with values outside 1.5 times the interquartile range

You want to highlight cells in column D that are outliers, defined as values less than Q1 - 1.5*IQR or greater than Q3 + 1.5*IQR, where IQR = Q3 - Q1. Which custom formula correctly identifies these outliers?

A =OR(D1 < QUARTILE(D:D, 1) - 1.5 * (QUARTILE(D:D, 3) - QUARTILE(D:D, 1)), D1 > QUARTILE(D:D, 3) + 1.5 * (QUARTILE(D:D, 3) - QUARTILE(D:D, 1)))

B =OR(D1 < QUARTILE(D:D, 2) - 1.5 * (QUARTILE(D:D, 4) - QUARTILE(D:D, 1)), D1 > QUARTILE(D:D, 4) + 1.5 * (QUARTILE(D:D, 4) - QUARTILE(D:D, 1)))

C =OR(D1 < MEDIAN(D:D) - 1.5 * (MAX(D:D) - MIN(D:D)), D1 > MEDIAN(D:D) + 1.5 * (MAX(D:D) - MIN(D:D)))

D =OR(D1 < PERCENTILE(D:D, 0.25) - 1.5 * (PERCENTILE(D:D, 0.75) - PERCENTILE(D:D, 0.25)), D1 > PERCENTILE(D:D, 0.75) + 1.5 * (PERCENTILE(D:D, 0.75) - PERCENTILE(D:D, 0.25)))

💡 Hint

Recall the definition of quartiles and interquartile range (IQR).

Option A uses QUARTILE(D:D, 1) for Q1 and QUARTILE(D:D, 3) for Q3, then calculates IQR and checks if the value is outside the 1.5*IQR range. This matches the standard outlier definition.

Option A uses median and range, which is incorrect for IQR.

Option A uses quartile 2 (median) and quartile 4 (max), which is incorrect.

Option A uses PERCENTILE which is equivalent but QUARTILE is more common for this rule.

📊 Formula Result

expert

Custom formula to highlight rows where sum of columns E and F exceeds 100

You want to highlight entire rows where the sum of values in columns E and F is greater than 100. You apply a custom formula-based rule starting from row 2. Which formula should you use to correctly highlight these rows?

A =SUM(E2:F2)>100

B =SUM($E2:$F2)>100

C =$E$2+$F$2>100

D =$E2+$F2>100

💡 Hint

Think about how to sum values in columns E and F for each row dynamically.

Option B uses =SUM($E2:$F2)>100 which sums columns E and F for the current row (row 2 initially) and compares to 100. The dollar signs fix the columns but allow the row number to change as the rule applies to other rows.

Option B adds the two cells but does not use SUM; it works but is less flexible.

Option B uses absolute references for row 2, so it always checks the same row, not dynamic.

Option B sums without fixing columns, which can cause errors if the rule applies to other columns.