Dynamic charts with data ranges in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to create a dynamic named range for the Sales data in column B starting from B2.

Excel

=OFFSET(B2, 0, 0, [1], 1)

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

ACOUNTA(B2:B100)

BSUM(B2:B100)

CAVERAGE(B2:B100)

DMAX(B2:B100)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of COUNTA, which sums values rather than counting cells.

Using AVERAGE or MAX which do not return the count of cells.

The OFFSET function needs the height of the range, which is the count of non-empty cells in B2:B100. COUNTA counts non-empty cells, making the range dynamic.

2fill in blank

medium

Complete the formula to create a dynamic named range for the Dates in column A starting from A2.

Excel

=OFFSET(A2, 0, 0, [1], 1)

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

ACOUNT(A2:A100)

BCOUNTA(A2:A100)

CSUM(A2:A100)

DMAX(A2:A100)

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT which ignores text cells.

Using SUM or MAX which do not count cells.

Dates are text or numbers, but to count all non-empty cells including text, COUNTA is used. COUNT counts only numbers, which might miss dates stored as text.

3fill in blank

hard

Fix the error in the formula to create a dynamic range for Sales in column B starting at B2.

Excel

=OFFSET(B2, 0, 0, COUNTA(B:B)[1]1)

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

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving no operator between COUNTA and 1.

Using plus sign which causes wrong range size.

The formula has an error because it tries to add 1 directly after the COUNTA function without an operator. The correct fix is to subtract 1 to adjust the count if needed or to remove the extra number. Here, subtracting 1 fixes the syntax.

4fill in blank

hard

Fill both blanks to create a dynamic range formula for the Sales data in column B starting at B2 with variable length.

Excel

=OFFSET(B2, 0, 0, [1], [2])

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

ACOUNTA(B2:B100)

B1

C0

DSUM(B2:B100)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of COUNTA for height.

Using 0 for width which results in an empty range.

The height of the range is the count of non-empty cells in B2:B100 using COUNTA. The width is 1 because the data is in a single column.

5fill in blank

hard

Fill all three blanks to create a dynamic named range for Dates in column A starting at A2, with height based on non-empty cells and width of 1.

Excel

=OFFSET([1], 0, 0, [2], [3])

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

AA2

BCOUNTA(A2:A100)

C1

DSUM(A2:A100)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of COUNTA for height.

Using wrong starting cell or width.

The starting cell is A2. The height is the count of non-empty cells in A2:A100 using COUNTA. The width is 1 because the range is a single column.