Mixed references ($A1, A$1) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to keep the column fixed but allow the row to change when copied.

Excel

=SUM([1]1:B1)

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

AA$

B$A

CA

D$B

Attempts:

3 left

💡 Hint

Common Mistakes

Using A$1 fixes the row, not the column.

Not using any dollar sign allows both row and column to change.

Using $A1 fixes the column A but allows the row number to change when copied.

2fill in blank

medium

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

Excel

=AVERAGE(A[1]:D5)

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

A5

B$D

C$5

DD$

Attempts:

3 left

💡 Hint

Common Mistakes

Using $5 fixes neither column nor row correctly.

Using 5 without dollar sign allows row to change.

Using A$5 fixes the row 5 but allows the column to change when copied.

3fill in blank

hard

Fix the error in the formula to correctly fix the column and allow the row to change.

Excel

=SUM([1]1:C1)

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

A$A

BA$

CA

D$1

Attempts:

3 left

💡 Hint

Common Mistakes

Using A$ fixes the row, not the column.

Using $1 is invalid as a column reference.

The correct mixed reference to fix the column A and allow the row to change is $A1.

4fill in blank

hard

Fill both blanks to create a formula that fixes the row in the first cell and fixes the column in the second cell.

Excel

=SUM(A[1]:[2]1)

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

A$5

BB

C$C

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using no dollar signs allows both row and column to change.

Mixing up which part to fix causes wrong references.

A$5 fixes the row 5 in the first cell, and $C1 fixes the column C in the second cell.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps each column letter to a mixed reference fixing the row 10 but allowing the column to change.

Excel

{ [1]: [2][3] for [1] in ['A', 'B', 'C'] }

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

Acol

B$

C10

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names for key and value.

Not fixing the row number with a dollar sign.

The variable col is used as key and in the value. The value uses col$10 to fix row 10 but allow column to change. Since col is a string like 'A', the correct mixed reference is col & "$10" in Excel formulas, but here we simulate with string keys and values.