Array formulas basics in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to multiply each value in range A1:A5 by 2 using an array formula.

Excel

=A1:A5[1]2

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

A/

B+

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using + instead of * will add 2 to each element, not multiply.

Using / will divide each element by 2, which is incorrect here.

The asterisk (*) is used to multiply each element in the range by 2 in an array formula.

2fill in blank

medium

Complete the formula to sum the product of ranges A1:A3 and B1:B3 using an array formula.

Excel

=SUM(A1:A3[1]B1:B3)

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

A*

B-

C/

D+

Attempts:

3 left

💡 Hint

Common Mistakes

Using + will add elements instead of multiplying.

Using / or - will not produce the correct product sum.

The multiplication operator (*) multiplies corresponding elements of the two ranges before summing them.

3fill in blank

hard

Fix the error in the array formula that calculates the square of each value in A1:A4.

Excel

=A1:A4[1]2

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

A^

B**

C*

D+

Attempts:

3 left

💡 Hint

Common Mistakes

Using ** is common in some languages but not in Excel.

Using * will multiply but not raise to a power.

In Excel, the caret (^) is used for exponentiation, so raising each element to the power of 2 uses ^2.

4fill in blank

hard

Fill both blanks to create an array formula that returns TRUE for values in A1:A5 greater than 10.

Excel

=(A1:A5 [1] 10)

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

A<

B>

CTRUE

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using < will check for less than, which is the opposite.

Using FALSE will not correctly indicate values greater than 10.

The greater than operator (>) compares each value to 10, and the formula returns TRUE for those greater.

5fill in blank

hard

Fill all three blanks to create an array formula that sums values in A1:A5 only if they are greater than 5.

Excel

=SUM(IF(A1:A5 [1] 5, A1:A5, [2]))

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

A>

B<

C0

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using < will select values less than 5, which is incorrect.

Using FALSE instead of 0 will cause errors in summing.

The IF checks if values are greater than 5 (using >), returns the value if true, else 0 to sum only those greater than 5.

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