Mixed references in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to lock the column but allow the row to change when copied down.

Google Sheets

=SUM([1]1:A10)

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

AA

BA$

C$A

D$A$

Attempts:

3 left

💡 Hint

Common Mistakes

Using $ before the row number instead of the column letter.

Locking both column and row with $A$1 which prevents any change.

Using $A locks the column A, but allows the row number to change when copied down.

2fill in blank

medium

Complete the formula to lock the row but allow the column to change when copied across.

Google Sheets

=AVERAGE(B[1]:E5)

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

A5$

B$5

C5

D$E

Attempts:

3 left

💡 Hint

Common Mistakes

Putting $ after the row number like 5$, which is invalid.

Locking both column and row with $E$5.

Using $5 locks the row 5, so when copied across columns, the row stays fixed.

3fill in blank

hard

Fix the error in the formula to correctly lock the column B but allow the row to change.

Google Sheets

=SUM(B[1]:B10)

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

A$2

B2$

C$B2

DB$2

Attempts:

3 left

💡 Hint

Common Mistakes

Putting $ after the row number.

Locking both column and row accidentally.

Using B$2 locks the row 2, but here we want to lock the column B only, so $B2 is incorrect. The correct is $B2 but since the code is B{{BLANK_1}}, the blank should be $2 to lock the row 2. However, the question asks to lock the column B only, so the correct is $2 to lock the row 2 is wrong. The correct answer is $2 to lock the row 2, but the question is about locking column B only. The correct answer is $B2 but the blank is after B, so the correct is $2. So the best fit is $2.

4fill in blank

hard

Fill both blanks to create a formula that locks the column A and the row 1 in the reference.

Google Sheets

=IF($[1]1 > 10, $A$[2], 0)

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

AA

B1

C2

DB

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column letter in first blank.

Not locking the row number in second blank.

The first blank should be B to compare column B row 1. The second blank is 1 to lock row 1 in the reference $A$1.

5fill in blank

hard

Fill all three blanks to create a formula that sums values from a fixed column B and a variable row.

Google Sheets

=SUM($[1]$[2]:[3]10)

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

AB

B2

DC

Attempts:

3 left

💡 Hint

Common Mistakes

Not locking the column or row correctly.

Using wrong column letter in the range end.

The first blank locks column B with B, the second locks row 2 with 2, and the third is column C for the range end.