Formula structure and cell references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula with mixed references?

Given the following values:

A1 = 5
B1 = 10

And the formula in C1:

=A1 + $B$1

What will be the value in C2 if the formula from C1 is copied down to C2?

Google Sheets

=A1 + $B$1

A 5

B 10

C 15

D Error

💡 Hint

Remember that $ signs lock the reference when copying formulas.

When copying the formula down, A1 changes to A2 (which is empty or 0), but $B$1 stays fixed at B1 (10). So in C2, the formula is =A2 + $B$1 = 0 + 10 = 10. Since A2 is empty, it counts as 0. So the correct answer is 10.

❓ Function Choice

intermediate

Which formula correctly sums cells A1 to A5 using absolute references?

You want to sum cells A1 to A5 and keep the range fixed when copying the formula to other cells. Which formula should you use?

A =SUM($A$1:$A$5)

B =SUM(A1:A5)

C =SUM($A1:$A5)

D =SUM(A$1:A$5)

💡 Hint

Absolute references use $ before both column and row.

Only =SUM($A$1:$A$5) locks both the column and row references, so the range stays fixed when copying the formula.

📊 Formula Result

advanced

What is the output of this formula with mixed relative and absolute references?

Given the values:

A1=2, B1=3, A2=4, B2=5

Formula in C1:

=A1*$B$1

What is the value in C2 after copying the formula from C1 to C2?

Google Sheets

=A1*$B$1

A 12

B 10

C 6

D Error

💡 Hint

Check how relative and absolute references change when copying formulas.

In C2, the formula becomes =A2*$B$1. A2 is 4, $B$1 is fixed at B1 which is 3. So 4*3=12.

🎯 Scenario

advanced

You want to multiply each value in column A by the value in B1 and copy the formula down. Which formula should you enter in C1?

Column A has numbers in A1 to A5. Cell B1 has a multiplier. You want to write a formula in C1 that multiplies A1 by B1 and then copy it down to C5, so each row multiplies the corresponding A cell by B1. Which formula should you use?

A =A1*B1

B =$A$1*$B$1

C =A1*$B$1

D =$A1*B$1

💡 Hint

Think about which references should stay fixed and which should change when copying down.

To multiply each A cell by the fixed B1, the formula should have A1 relative (changes when copied down) and B1 absolute (fixed). So =A1*$B$1 is correct.

❓ data_analysis

expert

How many cells will contain the value 100 after copying this formula?

You have values in A1:A5 as 10, 20, 30, 40, 50 respectively. In B1 you have 10. You enter the formula =A1*$B$1 in C1 and copy it down to C5.

How many cells in C1:C5 will have the value 100?

Google Sheets

=A1*$B$1

A 2

B 0

C 3

D 1

💡 Hint

Calculate each product and count how many equal 100.

Multiplying each A cell by 10: 10*10=100, 20*10=200, 30*10=300, 40*10=400, 50*10=500. Only C1 equals 100.

Practice

1. What does a formula in Google Sheets always start with?

easy

A. A dollar sign $

B. A plus sign +

C. An equal sign =

D. A hashtag #

Solution

1. Step 1: Identify the starting character for formulas

   In Google Sheets, every formula must begin with an equal sign = to tell the sheet to calculate something.

2. Step 2: Identify the correct starting character

   Only the equal sign = is used to start formulas; other symbols like +, $, or # do not start formulas.

3. Final Answer:

   An equal sign = -> Option C

4. Quick Check:

   Formula start = '=' [OK]

Hint: Remember: formulas always begin with '=' sign [OK]

Common Mistakes:

• Starting formulas with '+' instead of '='
• Confusing '$' for formula start
• Using '#' thinking it starts formulas

2. Which of these is the correct absolute reference to cell B3 in a formula?

easy

A. $B$3

B. B-3

C. 3B

D. B3

Solution

1. Step 1: Identify the absolute reference syntax

   Cell B3 is a relative reference. Adding dollar signs $B$3 makes it absolute, fixing the column and row.

2. Step 2: Identify correct syntax for referencing

   3B and B-3 are invalid references. Both B3 and $B$3 are valid, but absolute references use dollar signs.

3. Final Answer:

   $B$3 -> Option A

4. Quick Check:

   Absolute reference = '$B$3' [OK]

Hint: Use $ to fix column and row in cell references [OK]

Common Mistakes:

• Writing row before column like '3B'
• Using minus sign in cell reference
• Confusing relative and absolute references

3. Given the formula =A1 + B1 where A1=5 and B1=3, what is the result?

medium

A. 8

B. A1+B1

C. 53

D. Error

Solution

1. Step 1: Substitute cell values into the formula

   Replace A1 with 5 and B1 with 3, so the formula becomes =5 + 3.

2. Step 2: Calculate the sum

   Adding 5 and 3 gives 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add numbers from referenced cells to get result [OK]

Common Mistakes:

• Treating formula as text and not calculating
• Concatenating numbers as text (getting 53)
• Getting error due to wrong formula syntax

4. The formula =SUM(A1:A3 B1:B3) gives an error. What is the fix?

medium

A. Change SUM to SUMIF

B. Add a comma between ranges: =SUM(A1:A3, B1:B3)

C. Remove the equal sign

D. Use semicolon instead of colon

Solution

1. Step 1: Identify the missing comma in SUM

   The formula is missing a comma between the two ranges inside SUM, causing a syntax error.

2. Step 2: Correct the formula by adding a comma

   Adding a comma between A1:A3 and B1:B3 fixes the syntax: =SUM(A1:A3, B1:B3).

3. Final Answer:

   =SUM(A1:A3, B1:B3) -> Option B

4. Quick Check:

   Separate ranges with commas in SUM [OK]

Hint: Separate multiple ranges with commas inside functions [OK]

Common Mistakes:

• Forgetting commas between ranges
• Removing equal sign thinking it's optional
• Using wrong function like SUMIF without criteria

5. You want to create a formula that adds the value in cell C2 to the sum of cells A1 to A3, but always uses the value in C2 even if you copy the formula elsewhere. Which formula should you use?

hard

A. =SUM($A$1:$A$3) + $C2

B. =SUM(A1:A3) + C2

C. =SUM($A$1:$A$3) + C2

D. =SUM(A1:A3) + $C$2

Solution

1. Step 1: Identify the absolute reference for C2

   To keep the reference to C2 fixed when copying the formula, use absolute reference $C$2.

2. Step 2: Use relative reference for A1:A3 sum

   The range A1:A3 can stay relative so it adjusts if needed, but the question focuses on fixing C2.

3. Final Answer:

   =SUM(A1:A3) + $C$2 -> Option D

4. Quick Check:

   Fix C2 with $ for absolute reference [OK]

Hint: Use $ before column and row to fix cell reference [OK]

Common Mistakes:

• Not using $ and losing fixed reference on copy
• Fixing range unnecessarily when not required
• Using partial absolute references incorrectly