Why advanced formulas solve complex problems in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum values in cells A1 to A5.

Excel

=SUM([1])

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

AA1+A5

BA1-A5

CA1;A5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) instead of a colon for ranges.

Separating cells with semicolons (;).

Adding cells with plus (+) instead of using a range.

The correct way to specify a range in Excel is using a colon (:), so A1:A5 sums all cells from A1 to A5.

2fill in blank

medium

Complete the formula to find the average of numbers in cells B1 to B10.

Excel

=AVERAGE([1])

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

AB1-B10

BB1:B10

CB1;B10

DB1+B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) instead of a colon.

Separating cells with semicolons (;).

Adding only two cells instead of a range.

Using B1:B10 selects the range of cells from B1 to B10 for the AVERAGE function.

3fill in blank

hard

Fix the error in the formula to count how many cells in C1 to C20 contain numbers.

Excel

=COUNT([1])

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

AC1-C20

BC1;C20

CC1:C20

DC1+C20

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) or semicolon (;) instead of a colon (:).

Adding two cells instead of specifying a range.

The COUNT function needs a proper range, which is C1:C20 using a colon.

4fill in blank

hard

Fill both blanks to create a formula that sums values in D1 to D10 only if they are greater than 5.

Excel

=SUMIF([1], [2])

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

AD1:D10

B>5

C<5

DD1;D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolons instead of colons for ranges.

Using the wrong comparison operator in the condition.

The SUMIF function sums cells in the range D1:D10 that meet the condition >5.

5fill in blank

hard

Fill all three blanks to create a formula that counts cells in E1 to E15 containing text starting with 'A'.

Excel

=COUNTIF([1], [2] & "[3]")

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

AE1:E15

B"A"

C*

DE1;E15

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolons instead of colons for ranges.

Forgetting to use quotes around the letter 'A'.

Not using the wildcard * to match any characters after 'A'.

The COUNTIF function counts cells in E1:E15 where the text starts with A. The asterisk (*) is a wildcard meaning any characters after 'A'.