Named ranges in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Using Named Ranges in SUM Formula

You have a named range Sales referring to cells B2:B6 containing numbers. What is the result of the formula =SUM(Sales) if the cells contain 10, 20, 30, 40, and 50 respectively?

A 100

B 50

C Syntax Error

D 150

💡 Hint

Think about what the SUM function does with a range of numbers.

The named range 'Sales' refers to cells with values 10, 20, 30, 40, and 50. SUM adds all these values: 10+20+30+40+50 = 150.

❓ Function Choice

intermediate

Choosing the Correct Function with Named Ranges

You have a named range Prices referring to C2:C10. Which formula correctly calculates the average price?

A =AVERAGE(Prices)

B =SUM(Prices)

C =COUNT(Prices)

D =MAX(Prices)

💡 Hint

Average means adding all values and dividing by the count.

The AVERAGE function calculates the mean of the numbers in the named range 'Prices'. SUM adds all values, COUNT counts how many cells have numbers, MAX finds the largest number.

🎯 Scenario

advanced

Impact of Changing Named Range Reference

You have a named range Data referring to D2:D5. The formula =SUM(Data) currently returns 100. If you change the named range Data to refer to D2:D7, what will happen to the formula result?

A The sum will include more cells, so the result will likely increase.

B The sum will stay the same because the formula does not update automatically.

C The formula will return a #REF! error because the range changed.

D The sum will decrease because the range is larger.

💡 Hint

Think about what happens when you include more cells in a sum.

Changing the named range to include more cells means SUM(Data) now adds more values, so the total will likely increase unless new cells are zero or negative.

📊 Formula Result

advanced

Using Named Ranges with INDIRECT Function

You have a named range List1 referring to A1:A3 with values 5, 10, 15. What is the result of =SUM(INDIRECT("List1"))?

A #REF! error

B 0

C 30

D 15

💡 Hint

INDIRECT converts text to a reference, so it uses the named range.

INDIRECT("List1") returns the range A1:A3. SUM adds 5+10+15 = 30.

❓ data_analysis

expert

Analyzing Named Range Usage in a Complex Formula

Given named ranges: Costs = B2:B6 (values: 100, 200, 150, 300, 250), Discounts = C2:C6 (values: 10, 20, 15, 30, 25). What is the result of the formula =SUMPRODUCT(Costs, Discounts)/SUM(Discounts)?

A 210

B 225

C Syntax Error

D 200

💡 Hint

SUMPRODUCT multiplies pairs and sums them; then divide by total discounts.

SUMPRODUCT multiplies each cost by its discount: (100*10)+(200*20)+(150*15)+(300*30)+(250*25) = 1000+4000+2250+9000+6250 = 22500. SUM(Discounts) = 10+20+15+30+25 = 100. Divide 22500 by 100 = 225.