Array formulas basics in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of an Array SUM Formula

Given the range A1:A5 contains the numbers 1, 2, 3, 4, 5 respectively, what is the output of the formula =SUM(A1:A5*2) when entered as an array formula (Ctrl+Shift+Enter)?

A 55

B 30

C 15

D 10

💡 Hint

Remember that multiplying a range by 2 doubles each element before summing.

The formula multiplies each number in A1:A5 by 2, resulting in {2,4,6,8,10}. Then SUM adds them: 2+4+6+8+10 = 30.

❓ Function Choice

intermediate

Choosing the Correct Array Formula Function

You want to count how many numbers in the range B1:B10 are greater than 5 using an array formula. Which formula will give the correct count?

A =SUM(B1:B10>5)

B =ARRAYFORMULA(COUNT(B1:B10>5))

C =SUM(IF(B1:B10>5,1,0))

D =COUNTIF(B1:B10, ">5")

💡 Hint

Think about how to convert TRUE/FALSE to numbers for summing.

Option C uses IF to convert TRUE/FALSE to 1/0, then sums them. This works as an array formula to count values greater than 5.

Option C sums TRUE/FALSE directly, but Excel treats TRUE as 1 only in array context, so it needs to be entered as an array formula.

Option C is not an array formula but a normal function.

Option C is invalid syntax in Excel.

📊 Formula Result

advanced

Result of an Array Formula with Multiple Operations

If cells C1:C4 contain {10, 20, 30, 40} and D1:D4 contain {5, 15, 25, 35}, what is the result of the array formula =SUM((C1:C4-D1:D4)^2)?

A 100

B 70

C 60

D 50

💡 Hint

Calculate each difference, square it, then sum all.

Differences: (10-5)=5, (20-15)=5, (30-25)=5, (40-35)=5

Squares: 25, 25, 25, 25

Sum: 25+25+25+25=100

🎯 Scenario

advanced

Using Array Formulas to Extract Unique Values

You have a list of names in E1:E10 with some duplicates. Which array formula will return the unique names sorted alphabetically in a spill range (Excel 365 or later)?

A =SORT(UNIQUE(E1:E10))

B =UNIQUE(SORT(E1:E10))

C =FILTER(E1:E10, COUNTIF(E1:E10, E1:E10)=1)

D =SORT(FILTER(E1:E10, E1:E10<>""))

💡 Hint

Think about first removing duplicates, then sorting.

Option A first extracts unique names, then sorts them alphabetically.

Option A sorts first then extracts unique, which also works but is less efficient.

Option A filters only names that appear once, excluding duplicates entirely.

Option A sorts all names including duplicates.

🧠 Conceptual

expert

Understanding Array Formula Behavior with Mixed Data Types

Given the range F1:F5 contains {10, "20", TRUE, FALSE, "text"}, what is the result of the array formula =SUM(F1:F5*1)?

A Error

B 10

C 11

D 31

💡 Hint

Remember how Excel coerces text and logical values to numbers in math operations.

Excel coerces "20" to 20, TRUE to 1, FALSE to 0, "text" to 0 in math operations.

So values become: 10, 20, 1, 0, 0

Sum: 10+20+1+0+0 = 31