Named ranges in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Using Named Ranges in SUM Formula

You have a named range SalesData referring to cells B2:B10. What is the result of the formula =SUM(SalesData) if the cells contain the numbers 1 through 9?

A 45

B 0

C 9

D Error: Named range not found

💡 Hint

Recall that SUM adds all numbers in the range.

The named range SalesData covers cells B2 to B10, which contain numbers 1 to 9. The sum of numbers 1 through 9 is 45.

❓ Function Choice

intermediate

Choosing the Correct Function with Named Ranges

You have a named range Expenses referring to C2:C12. You want to find the average expense. Which formula correctly uses the named range?

A =AVERAGE(Expenses)

B =COUNT(Expenses)

C =SUM(Expenses)/COUNTIF(Expenses, ">0")

D =MAX(Expenses)

💡 Hint

Think about which function calculates the average directly.

The AVERAGE function calculates the mean of the numbers in the named range Expenses. Other options either count or sum but do not directly calculate the average.

🎯 Scenario

advanced

Impact of Moving Named Range Cells

You have a named range DataRange referring to D2:D6. You insert a new row above row 2. What will DataRange refer to after the insertion?

A Error: Named range invalid

B D2:D6

C D1:D5

D D3:D7

💡 Hint

Think about how named ranges adjust when rows are inserted.

When you insert a row above the first row of a named range, the named range shifts down by one row. So D2:D6 becomes D3:D7.

❓ data_analysis

advanced

Using Named Ranges in Conditional Formatting

You have a named range Scores referring to E2:E20. You want to highlight cells in E2:E20 that are above the average score. Which custom formula should you use in conditional formatting?

A =E2>AVERAGE(E$2:E$20)

B =Scores>AVERAGE(E2:E20)

C =E2>AVERAGE(Scores)

D =E2>AVERAGE(E2:E20)

💡 Hint

Remember to use relative references correctly in conditional formatting formulas.

The formula =E2>AVERAGE(Scores) correctly compares each cell in the range to the average of the named range. Using Scores makes the formula dynamic and clear.

🧠 Conceptual

expert

Named Range Scope and Usage

In Google Sheets, what is the scope of a named range created in a sheet, and how does it affect its usage in formulas across different sheets?

A Named ranges have sheet-level scope and can only be used in formulas on the sheet where they were created.

B Named ranges have spreadsheet-wide scope and can be used in formulas on any sheet.

C Named ranges have scope limited to the first sheet and cannot be used elsewhere.

D Named ranges must be redefined on each sheet to be used in formulas.

💡 Hint

Think about how named ranges simplify formulas across sheets.

In Google Sheets, named ranges are defined for the entire spreadsheet, so you can use them in formulas on any sheet without redefining them.

Practice

1. What is the main purpose of using named ranges in Google Sheets?

easy

A. To give a simple, memorable name to a group of cells for easier use in formulas

B. To change the color of cells automatically

C. To sort data alphabetically

D. To protect cells from editing

Solution

1. Step 1: Understand what named ranges do

   Named ranges assign a friendly name to a cell or range of cells, making formulas easier to read.

2. Step 2: Compare options to the purpose

   Only To give a simple, memorable name to a group of cells for easier use in formulas describes naming cells for easier formula use; others describe unrelated features.

3. Final Answer:

   To give a simple, memorable name to a group of cells for easier use in formulas -> Option A

4. Quick Check:

   Named ranges = easy formula reference [OK]

Hint: Named ranges simplify formulas by replacing cell addresses [OK]

Common Mistakes:

• Thinking named ranges change cell colors
• Confusing named ranges with sorting or protection

2. Which of the following is the correct way to use a named range called SalesData in a formula to sum its values?

easy

A. =SUM[#SalesData]

B. =SUM('SalesData')

C. =SUM(SalesData)

D. =SUM(SalesData!)

Solution

1. Step 1: Recall named range usage syntax

   Named ranges are used directly by their name without quotes or special characters in formulas.

2. Step 2: Evaluate each option

   =SUM(SalesData) uses =SUM(SalesData), which is correct. Options B, C, and D use invalid syntax with quotes, brackets, or exclamation marks.

3. Final Answer:

   =SUM(SalesData) -> Option C

4. Quick Check:

   Use named range name directly in formulas [OK]

Hint: Use named ranges directly without quotes or symbols [OK]

Common Mistakes:

• Adding quotes around named ranges
• Using brackets or exclamation marks incorrectly

3. Given a named range Prices referring to cells A1:A5 with values {10, 20, 30, 40, 50}, what is the result of the formula =AVERAGE(Prices)?

medium

A. 50

B. 150

C. 10

D. 30

Solution

1. Step 1: Understand the named range values

   The named range Prices contains values 10, 20, 30, 40, and 50.

2. Step 2: Calculate the average of these values

   Sum = 10+20+30+40+50 = 150; Count = 5; Average = 150 / 5 = 30.

3. Final Answer:

   30 -> Option D

4. Quick Check:

   Average of Prices = 30 [OK]

Hint: Sum values then divide by count for average [OK]

Common Mistakes:

• Summing values instead of averaging
• Using wrong range or forgetting count

4. You created a named range Expenses for cells B1:B4. Your formula =SUM(Expense) returns #NAME? instead of the expected sum. What is the likely problem?

medium

A. SUM function does not work with ranges

B. The named range is misspelled in the formula

C. The cells in the named range are empty

D. Named ranges cannot be used in SUM formulas

Solution

1. Step 1: Check formula spelling against named range

   The named range is named Expenses, but the formula uses Expense (missing 's').

2. Step 2: Understand effect of misspelling

   A misspelled named range is undefined, causing a #NAME? error in the formula.

3. Final Answer:

   The named range is misspelled in the formula -> Option B

4. Quick Check:

   Correct spelling of named range needed [OK]

Hint: Check exact spelling of named ranges in formulas [OK]

Common Mistakes:

• Assuming named ranges can't be used in SUM
• Ignoring spelling differences
• Thinking empty cells cause zero sum always

5. You have two named ranges: Income (C1:C3) with values {1000, 1200, 1100} and Expenses (D1:D3) with values {400, 500, 450}. Which formula correctly calculates the net total (Income minus Expenses) for each row and sums the results?

hard

A. =SUMPRODUCT(Income - Expenses)

B. =Income - Expenses

C. =SUM(Income) - Expenses

D. =SUM(Income + Expenses)

Solution

1. Step 1: Understand the goal

   We want to subtract Expenses from Income row by row, then sum all results.

2. Step 2: Evaluate each option

   =Income - Expenses subtracts ranges element-wise but spills an array of differences without summing. =SUMPRODUCT(Income - Expenses) subtracts element-wise and sums to a single value, correct. =SUM(Income) - Expenses subtracts total Income from each Expense, spilling an array, incorrect. =SUM(Income + Expenses) adds instead of subtracts.

3. Step 3: Choose best formula for row-wise net sum

   SUMPRODUCT(Income - Expenses) calculates (1000-400)+(1200-500)+(1100-450)=600+700+650=1950.

4. Final Answer:

   =SUMPRODUCT(Income - Expenses) -> Option A

5. Quick Check:

   Row-wise subtraction then sum = SUMPRODUCT [OK]

Hint: Use SUMPRODUCT for element-wise operations on named ranges [OK]

Common Mistakes:

• Using SUM with range subtraction directly
• Confusing total sums with row-wise sums
• Adding instead of subtracting ranges