Managing multiple rules in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to apply a conditional formatting rule that highlights cells greater than 100.

Excel

=IF(A1 [1] 100, TRUE, FALSE)

Drag options to blanks, or click blank then click option'

A<

B<=

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will highlight cells less than 100.

Using '=' will only highlight cells exactly equal to 100.

The formula checks if the value in cell A1 is greater than 100. If yes, it returns TRUE to apply the formatting.

2fill in blank

medium

Complete the formula to highlight cells that are between 50 and 150 inclusive.

Excel

=AND(A1 [1] 50, A1 [2] 150)

Drag options to blanks, or click blank then click option'

A>=

B<=

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' or '<' alone excludes boundary values.

Mixing up the operators will highlight wrong cells.

The formula uses AND to check if A1 is greater than or equal to 50 and less than or equal to 150, so cells in this range are highlighted.

3fill in blank

hard

Fix the error in the formula to highlight cells that are either less than 20 or greater than 80.

Excel

=OR(A1 [1] 20, A1 [2] 80)

Drag options to blanks, or click blank then click option'

A<

B>

C<=

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<=' or '>=' changes the range and may highlight unwanted cells.

Using AND instead of OR will not work for this condition.

The OR function highlights cells where A1 is less than 20 or greater than 80. Using '<' and '>' correctly sets these conditions.

4fill in blank

hard

Fill both blanks to create a formula that highlights cells with values divisible by 5 and greater than 10.

Excel

=AND(MOD(A1, [1]) = 0, A1 [2] 10)

Drag options to blanks, or click blank then click option'

A5

B>

C<

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using 3 in MOD checks divisibility by 3, not 5.

Using '<' instead of '>' changes the condition.

The MOD function checks if A1 divided by 5 leaves no remainder (divisible by 5). The AND function also checks if A1 is greater than 10.

5fill in blank

hard

Fill all three blanks to create a formula that highlights cells with text length greater than 4 and containing the letter 'a'.

Excel

=AND(LEN(A1) [1] 4, ISNUMBER(SEARCH([2], A1)), A1 [3] "")

Drag options to blanks, or click blank then click option'

A>

B"a"

C<>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for length check.

Not quoting the letter 'a' in SEARCH.

Using '=' instead of '<>' to check for non-empty cells.

The formula checks if the length of text in A1 is greater than 4, if 'a' is found in the text, and if A1 is not empty (<> means not equal to empty).