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.