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

Practice

1. What does an array formula in Excel do?
=SUM(A1:A3*B1:B3) is an example of what kind of formula?

easy

A. It only sums values in one column

B. It creates a chart from data

C. It calculates multiple values at once and returns a single result

D. It formats cells automatically

Solution

1. Step 1: Understand what array formulas do

   Array formulas perform calculations on multiple values or ranges at the same time instead of one cell at a time.

2. Step 2: Analyze the example formula

   The formula =SUM(A1:A3*B1:B3) multiplies each pair of cells from A1:A3 and B1:B3, then sums all results together, showing a single total.

3. Final Answer:

   It calculates multiple values at once and returns a single result -> Option C

4. Quick Check:

   Array formula = multiple calculations at once [OK]

Hint: Array formulas handle many values together, not just one [OK]

Common Mistakes:

• Thinking array formulas only work on one cell
• Confusing array formulas with charts or formatting
• Not realizing multiplication happens element-wise

2. Which of these is the correct way to enter an array formula in older Excel versions (before dynamic arrays)?

easy

A. Type the formula and press Ctrl + Shift + Enter

B. Type the formula and press Enter

C. Type the formula and press Alt + F4

D. Type the formula and press Ctrl + C

Solution

1. Step 1: Recall how array formulas were entered before dynamic arrays

   Older Excel versions required a special key combination to tell Excel the formula is an array formula.

2. Step 2: Identify the correct key combination

   The correct way was to press Ctrl + Shift + Enter after typing the formula, which adds curly braces around it.

3. Final Answer:

   Type the formula and press Ctrl + Shift + Enter -> Option A

4. Quick Check:

   Old array formula entry = Ctrl + Shift + Enter [OK]

Hint: Use Ctrl+Shift+Enter for array formulas in old Excel [OK]

Common Mistakes:

• Pressing only Enter for array formulas in old Excel
• Using unrelated shortcuts like Alt+F4 or Ctrl+C
• Not knowing the difference between old and new Excel

3. Given the data:
A1=2, A2=3, A3=4
B1=5, B2=6, B3=7
What is the result of the formula =SUM(A1:A3*B1:B3) entered as an array formula?

medium

A. Error

B. 19

C. 12

D. 56

Solution

1. Step 1: Multiply each pair of cells element-wise

   Calculate 2*5=10, 3*6=18, 4*7=28.

2. Step 2: Sum all the products

   Add 10 + 18 + 28 = 56.

3. Final Answer:

   56 -> Option D

4. Quick Check:

   Sum of products = 56 [OK]

Hint: Multiply pairs then sum for array multiplication [OK]

Common Mistakes:

• Adding values before multiplying
• Multiplying sums instead of element-wise
• Expecting a range output instead of a single sum

4. You entered the formula =A1:A3+B1:B3 in a single cell without pressing Ctrl+Shift+Enter in Excel 2016. What will happen?

medium

A. Excel sums all values and shows a single number

B. Excel returns a #VALUE! error

C. Excel spills the results into multiple cells automatically

D. Excel shows the sum of A1 and B1 only

Solution

1. Step 1: Understand Excel 2016 array formula behavior

   Excel 2016 does not support dynamic arrays, so it cannot spill results automatically.

2. Step 2: Check what happens without Ctrl+Shift+Enter

   Entering an array formula without Ctrl+Shift+Enter causes Excel to treat it as a normal formula, which leads to a #VALUE! error when adding ranges directly.

3. Final Answer:

   Excel returns a #VALUE! error -> Option B

4. Quick Check:

   Old Excel + no Ctrl+Shift+Enter = #VALUE! error [OK]

Hint: Press Ctrl+Shift+Enter for arrays in Excel 2016 [OK]

Common Mistakes:

• Expecting automatic spilling in Excel 2016
• Thinking Excel sums all values automatically
• Assuming partial sums without array entry

5. You want to multiply two columns A1:A4 and B1:B4 and get the product for each row in C1:C4 using a single formula in C1 that spills down. Which formula should you use in Excel 365?

hard

A. =A1:A4*B1:B4

B. =SUM(A1:A4*B1:B4)

C. =ARRAYFORMULA(A1:A4*B1:B4)

D. =PRODUCT(A1:A4,B1:B4)

Solution

1. Step 1: Understand dynamic array behavior in Excel 365

   Excel 365 automatically spills array results into adjacent cells when a formula returns multiple values.

2. Step 2: Choose the formula that returns element-wise products

   The formula =A1:A4*B1:B4 multiplies each pair of cells and spills results into C1:C4.

3. Final Answer:

   =A1:A4*B1:B4 -> Option A

4. Quick Check:

   Dynamic arrays spill element-wise multiplication [OK]

Hint: Use simple range multiplication for spilling in Excel 365 [OK]

Common Mistakes:

• Using SUM which returns a single total, not array
• Using ARRAYFORMULA which is Google Sheets function
• Using PRODUCT which multiplies all values into one number