Formula-based rules in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the result of this conditional formatting formula?

You apply this formula-based rule to cell A1:

=AND(A1>10, ISNUMBER(A1))

If A1 contains the value 15, what will the formula return?

A TRUE

B FALSE

C #VALUE!

D 0

💡 Hint

Think about what AND does when both conditions are true.

The formula checks if A1 is greater than 10 and is a number. Since 15 is a number and greater than 10, both conditions are true, so AND returns TRUE.

❓ Function Choice

intermediate

Which formula-based rule highlights cells with text starting with 'Sale'?

You want to create a formula-based rule that highlights cells in column B if the text starts with the word 'Sale'. Which formula should you use?

A =RIGHT(B1,4)="Sale"

B =LEFT(B1,4)="Sale"

C =SEARCH("Sale", B1)=1

D =ISNUMBER(FIND("Sale", B1))

💡 Hint

Check which function extracts the first characters of the text.

LEFT(B1,4) extracts the first 4 characters. If they equal "Sale", the formula returns TRUE and highlights the cell. SEARCH and FIND check for presence but not necessarily at the start.

📊 Formula Result

advanced

What is the output of this formula-based rule for cell C3?

Given the formula-based rule:

=OR(C3="", ISERROR(C3))

What will the formula return if C3 contains the formula =VLOOKUP(100, A1:B5, 2, FALSE) and the lookup value 100 is not found?

A TRUE

B FALSE

C #N/A error

D #VALUE! error

💡 Hint

Consider what ISERROR returns when a formula results in an error.

If VLOOKUP does not find 100, it returns #N/A error. ISERROR detects any error and returns TRUE. OR returns TRUE if either condition is TRUE, so the formula returns TRUE.

❓ data_analysis

advanced

How many cells will be highlighted by this formula-based rule?

You apply this formula-based rule to range D1:D10:

=AND(ISNUMBER(D1), D1>AVERAGE($D$1:$D$10))

If the values in D1:D10 are:
5, 8, 12, 3, 15, 7, 10, 6, 9, 4

How many cells will be highlighted?

A 6

B 3

C 5

D 4

💡 Hint

Calculate the average first, then count how many numbers are greater than it.

Average of values is (5+8+12+3+15+7+10+6+9+4)/10 = 7.9. Numbers greater than 7.9 are 8, 12, 15, 10, 9 (5 cells).

🎯 Scenario

expert

Which formula-based rule highlights cells with dates in the past 7 days?

You want to highlight cells in column E that contain dates within the last 7 days from today. Which formula-based rule will correctly do this?

A =E1>TODAY()-7

B =AND(E1>TODAY()-7, E1<TODAY())

C =E1>=TODAY()-7

D =AND(E1>=TODAY()-7, E1<=TODAY())

💡 Hint

Remember to include both start and end dates in the range.

Option D checks if the date is between today minus 7 days and today inclusive, correctly capturing the last 7 days. Other options miss the upper or lower bound or exclude today.