Why reference types affect formula behavior in Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

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

Google Sheets

=SUM([1])

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

ASUM(A1, A5)

BA1+A5

CA1-A5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition operator instead of a range.

Trying to sum only two cells instead of a range.

The correct way to sum a range of cells is to use a range reference like A1:A5. This tells the formula to include all cells from A1 through A5.

2fill in blank

medium

Complete the formula to multiply the value in cell B1 by 10 using an absolute reference for B1.

Google Sheets

= [1] * 10

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

A$B$1

BB1

CB$1

D$B1

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative references that change when copied.

Adding dollar sign to only column or only row.

Using $B$1 makes the reference absolute, so if you copy the formula elsewhere, it always points to cell B1.

3fill in blank

hard

Fix the error in the formula to correctly sum cells from C1 to C3 using an absolute column and relative row reference.

Google Sheets

=SUM([1])

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

AC1:C3

B$C1:$C3

C$C$1:$C$3

DC$1:C$3

Attempts:

3 left

💡 Hint

Common Mistakes

Making both column and row absolute.

Making rows absolute instead of column.

The reference $C1:$C3 fixes the column C absolutely but allows the rows to adjust if copied. This matches the requirement.

4fill in blank

hard

Fill both blanks to create a formula that multiplies the value in D2 by the value in an absolute cell E1.

Google Sheets

= [1] * [2]

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

AD2

B$E$1

CE1

DD$2

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative reference for E1 which changes when copied.

Using absolute reference for D2 which prevents copying properly.

The formula multiplies the relative cell D2 by the absolute cell $E$1. The absolute reference ensures the formula always uses the value in E1.

5fill in blank

hard

Fill both blanks to create a formula that sums the range from A1 to A{{BLANK_1}} and multiplies by the value in absolute cell B{{BLANK_2}}.

Google Sheets

=SUM(A1:A[1]) * B[2]

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

A5

B$3

C3

D$5

Attempts:

3 left

💡 Hint

Common Mistakes

Using relative row reference for the multiplier which changes when copied.

Using wrong row number in the sum range.

The formula sums cells from A1 to A5 and multiplies by the value in absolute cell B$5. The dollar sign before 5 makes the row absolute.