Advanced filtering criteria in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to filter values greater than 50 in column A.

Excel

=FILTER(A2:A10, A2:A10 [1] 50)

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

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' will select values less than 50, which is incorrect.

Using '=' will select only values equal to 50.

The formula filters values in A2:A10 that are greater than 50 using the > operator.

2fill in blank

medium

Complete the formula to filter rows where column B equals "Completed".

Excel

=FILTER(A2:C10, B2:B10 [1] "Completed")

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

A<>

B>

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<>' means not equal, which would exclude "Completed" rows.

Using '>' or '<' does not work for text equality.

The formula filters rows where the value in column B is exactly "Completed" using the = operator.

3fill in blank

hard

Fix the error in the formula to filter values in column C less than or equal to 100.

Excel

=FILTER(C2:C20, C2:C20 [1] 100)

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

A=>

B=>=

C==

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=>' or '=>=' is invalid syntax in Excel.

Using '==' is not recognized in Excel formulas.

The correct operator for 'less than or equal to' in Excel is <=.

4fill in blank

hard

Fill both blanks to filter rows where column A is greater than 10 and column B is less than 20.

Excel

=FILTER(A2:B15, (A2:A15 [1] 10) * (B2:B15 [2] 20))

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

A>

B<

C>=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using >= or <= changes the condition to include equal values.

Mixing up > and < operators.

The formula filters rows where column A values are greater than 10 and column B values are less than 20 using > and < operators.

5fill in blank

hard

Fill all three blanks to filter rows where column A is not equal to 5, column B is greater than or equal to 10, and column C is less than 50.

Excel

=FILTER(A2:C20, (A2:A20 [1] 5) * (B2:B20 [2] 10) * (C2:C20 [3] 50))

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

A<>

B>=

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of <> for not equal condition.

Using < instead of >= for column B condition.

The formula filters rows where column A is not equal to 5 (<>), column B is greater than or equal to 10 (>=), and column C is less than 50 (<).