Named ranges in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum the values in the named range called 'Sales'.

Google Sheets

=SUM([1])

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

ASales

BA1:A10

CSUM

DTotal

Attempts:

3 left

💡 Hint

Common Mistakes

Using cell references instead of the named range.

Trying to put quotes around the named range.

The named range 'Sales' can be used directly in formulas like =SUM(Sales) to sum all values in that range.

2fill in blank

medium

Complete the formula to calculate the average of the named range 'Expenses'.

Google Sheets

=AVERAGE([1])

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

ASUM

BExpenses

CA1:A20

DTotal

Attempts:

3 left

💡 Hint

Common Mistakes

Using cell references instead of the named range.

Using the wrong function like SUM instead of AVERAGE.

Use the named range 'Expenses' directly inside the AVERAGE function to calculate the average.

3fill in blank

hard

Fix the error in the formula to count how many cells in the named range 'Inventory' are greater than 10.

Google Sheets

=COUNTIF([1], ">10")

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

AInventory

B"Inventory"

CInventory()

DInventory[]

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the named range name.

Adding parentheses or brackets after the named range.

Named ranges should be used without quotes or parentheses in formulas. So use Inventory directly.

4fill in blank

hard

Fill both blanks to create a formula that sums values in the named range 'Revenue' only if they are greater than 100.

Google Sheets

=SUMIF([1], [2])

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

ARevenue

B"<100"

C">100"

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong named range.

Using the wrong comparison operator or missing quotes.

The formula =SUMIF(Revenue, ">100") sums all values in the named range 'Revenue' that are greater than 100.

5fill in blank

hard

Fill all three blanks to create a formula that sums values in the named range 'Revenue' where the corresponding values in 'Region' are 'North' and in 'Quarter' are 'Q1'.

Google Sheets

=SUMIFS([1], [2], "North", [3], "Q1")

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

ARevenue

BRegion

CQuarter

DExpenses

Attempts:

3 left

💡 Hint

Common Mistakes

Incorrect argument order.

Forgetting quotes around text criteria like "North".

The formula =SUMIFS(Revenue, Region, "North", Quarter, "Q1") sums values from 'Revenue' meeting both criteria using the parallel named ranges 'Region' and 'Quarter'.

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