Absolute references ($) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Understanding Absolute References in a SUM Formula

You have a table where cell B1 contains the value 10, and cells A2 to A4 contain 1, 2, and 3 respectively. You enter the formula =A2*$B$1 in cell B2 and then drag it down to B4. What is the value in cell B4 after dragging?

A 60

B 3

C 30

D 10

💡 Hint

Remember that $B$1 is an absolute reference and does not change when dragging.

The formula multiplies the value in column A by the fixed value in B1 (which is 10). For B4, A4 is 3, so 3 * 10 = 30.

❓ Function Choice

intermediate

Choosing the Correct Absolute Reference for a Discount Calculation

You want to calculate the discounted price for items listed in column A, where the discount rate is in cell C1. Which formula correctly applies the discount to the price in A2 and can be dragged down without changing the discount cell?

A =A2*(1-$C$1)

B =A2*(1-C1)

C =$A$2*(1-C1)

D =A$2*(1-$C1)

💡 Hint

The discount rate cell should not change when dragging the formula down.

Using $C$1 locks the discount rate cell so it stays fixed when dragging. Option A is correct.

❓ data_analysis

advanced

Analyzing the Effect of Mixed References in a Multiplication Table

You create a multiplication table where row 1 (B1:E1) contains numbers 1 to 4, and column A (A2:A5) contains numbers 1 to 4. In cell B2, you enter the formula = $A2 * B$1 and drag it across to E5. What is the value in cell D4?

A 8

B 9

C 16

D 12

💡 Hint

Look at how the mixed references lock either the row or the column when dragging.

In D4, $A2 locks column A but row changes to 4, so value is A4=4 (A2=1, A3=2, A4=3, A5=4). B$1 locks row 1 but column changes to D (4) (B1=1, C1=2, D1=3, E1=4). So 3 * 3 = 9.

🎯 Scenario

advanced

Fixing a Formula to Correctly Reference a Tax Rate Cell

You have a list of prices in column B and a tax rate in cell D1. You want to calculate the total price including tax in column C. The formula in C2 is =B2*(1+D1). When you drag this formula down, the tax rate reference changes incorrectly. Which formula fixes this issue?

A =B2*(1+$D1)

B =B2*(1+D$1)

C =$B2*(1+D1)

D =B2*(1+$D$1)

💡 Hint

The tax rate cell should stay fixed when dragging down.

Using $D$1 locks both column and row of the tax rate cell, so it does not change when dragging.

📊 Formula Result

expert

Determining the Result of a Complex Formula with Mixed Absolute References

In a spreadsheet, cell B1 contains 5, and cell C2 contains 3. You enter the formula =B$1 + $C2 in cell D3 and then drag it one cell right and one cell down to cell E4. What is the value in cell E4?

A 0

B 10

C 6

D 8

💡 Hint

Check how the mixed absolute references affect row and column changes when dragging.

Dragging right shifts the column of B$1 to C$1 (row fixed), and dragging down shifts the row of $C2 to $C3 (column fixed). C1 and C3 are empty (0), so C$1 + $C3 = 0 + 0 = 0.