ARRAYFORMULA function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of ARRAYFORMULA with simple addition

Given the values in column A as 1, 2, 3, 4 in cells A1 to A4, what will be the output in cells B1 to B4 after entering this formula in B1?

=ARRAYFORMULA(A1:A4 + 5)

Google Sheets

=ARRAYFORMULA(A1:A4 + 5)

A 1, 2, 3, 4

B 5, 6, 7, 8

C 6, 7, 8, 9

D Error: Cannot add number to range

💡 Hint

Think about adding 5 to each number in the range using ARRAYFORMULA.

ARRAYFORMULA applies the addition to each element in the range A1:A4, so each value is increased by 5.

❓ Function Choice

intermediate

Choose the correct ARRAYFORMULA to multiply two columns

You have numbers in columns A and B from rows 1 to 3. Which ARRAYFORMULA correctly multiplies each pair of numbers in columns A and B and outputs the results in column C?

A =ARRAYFORMULA(A1:A3 * B1:B3)

B =ARRAYFORMULA(A1 * B1:B3)

C =ARRAYFORMULA(A1:A3 + B1:B3)

D =ARRAYFORMULA(A1:A3 & B1:B3)

💡 Hint

Multiplying two ranges element-wise requires both ranges to be the same size.

Option A multiplies each element in A1:A3 with the corresponding element in B1:B3. Other options either multiply a single cell by a range, add instead of multiply, or concatenate.

🎯 Scenario

advanced

Using ARRAYFORMULA to conditionally modify a range

You want to add 10 to values in column A only if they are greater than 5, otherwise keep them unchanged. Which formula entered in B1 will do this for A1:A5?

A =ARRAYFORMULA(A1:A5 * (A1:A5 > 5) + 10)

B =ARRAYFORMULA(IF(A1:A5 > 5, A1:A5 + 10, A1:A5))

C =ARRAYFORMULA(IF(A1:A5 > 5, A1:A5 + 10))

D =ARRAYFORMULA(A1:A5 + IF(A1:A5 > 5, 10))

💡 Hint

Use IF inside ARRAYFORMULA to apply condition to each element.

Option B correctly uses IF to check each value and add 10 only if greater than 5, else returns original value. Option B is missing the else part, C is incomplete, and A produces wrong calculation.

❓ data_analysis

advanced

Counting non-empty cells with ARRAYFORMULA

You want to count how many cells in range A1:A10 are not empty using ARRAYFORMULA combined with another function. Which formula will give the correct count?

A =SUM(ARRAYFORMULA(LEN(A1:A10) > 0))

B =ARRAYFORMULA(COUNTIF(A1:A10, "<>"))

C =ARRAYFORMULA(COUNTA(A1:A10))

D =SUM(ARRAYFORMULA(ISBLANK(A1:A10)))

💡 Hint

LEN returns length of text, ISBLANK returns TRUE for empty cells.

Option A uses LEN to check length > 0 for each cell, producing TRUE/FALSE which SUM counts as 1/0. Option A is invalid syntax, C returns a single value not an array, D sums blanks instead of non-blanks.

📊 Formula Result

expert

Output of ARRAYFORMULA with mixed operations and text

Given column A contains numbers 1, 2, 3 in cells A1 to A3, what is the output in column B after entering this formula in B1?

=ARRAYFORMULA(IF(A1:A3 > 1, "High", "Low"))

Google Sheets

=ARRAYFORMULA(IF(A1:A3 > 1, "High", "Low"))

A High, High, High

B Error: Cannot compare range with number

C Low, Low, Low

D Low, High, High

💡 Hint

IF inside ARRAYFORMULA applies condition to each element in the range.

For A1=1 (not >1) output is "Low"; for A2=2 and A3=3 (both >1) output is "High".

Practice

1. What does the ARRAYFORMULA function do in Google Sheets?

easy

A. Applies a formula to an entire range of cells at once

B. Deletes all data in a selected range

C. Sorts data alphabetically

D. Converts numbers to text format

Solution

1. Step 1: Understand the purpose of ARRAYFORMULA

   ARRAYFORMULA lets you apply one formula to many cells without copying it manually.

2. Step 2: Compare options with ARRAYFORMULA's function

   Only Applies a formula to an entire range of cells at once describes applying a formula to a range at once, which matches ARRAYFORMULA's use.

3. Final Answer:

   Applies a formula to an entire range of cells at once -> Option A

4. Quick Check:

   ARRAYFORMULA = apply formula to range [OK]

Hint: Think: one formula, many cells at once [OK]

Common Mistakes:

• Confusing ARRAYFORMULA with sorting or deleting data
• Thinking it changes data format instead of applying formulas
• Assuming it works only on single cells

2. Which of the following is the correct syntax to use ARRAYFORMULA to multiply each value in column A by 2?

easy

A. =ARRAYFORMULA(A1:A * 2)

B. =ARRAYFORMULA(2 * A1)

C. =ARRAYFORMULA(A1:A + 2)

D. =ARRAYFORMULA(A1:A2 * 2)

Solution

1. Step 1: Identify correct range and operation

   We want to multiply all values in column A (A1:A) by 2, so the range must be A1:A and operation is * 2.

2. Step 2: Check each option's syntax

   =ARRAYFORMULA(A1:A * 2) correctly uses ARRAYFORMULA with A1:A * 2. =ARRAYFORMULA(2 * A1) uses only A1 (single cell), C adds 2 instead of multiplying, D uses A1:A2 which is only two cells.

3. Final Answer:

   =ARRAYFORMULA(A1:A * 2) -> Option A

4. Quick Check:

   ARRAYFORMULA(range * 2) = correct syntax [OK]

Hint: Use full column range like A1:A for all rows [OK]

Common Mistakes:

• Using a single cell reference instead of a range
• Using incorrect range like A1:A2 for whole column
• Mixing addition instead of multiplication

3. Given column A contains numbers 1, 2, 3 in cells A1, A2, A3 respectively, what will be the output of =ARRAYFORMULA(A1:A3 * 10)?

medium

A. [1, 2, 3, 10]

B. [10, 20, 30]

C. Error: Incorrect range

D. [10, 20]

Solution

1. Step 1: Understand the input range and operation

   The formula multiplies each value in A1:A3 by 10. Values are 1, 2, 3.

2. Step 2: Calculate each multiplication

   1*10=10, 2*10=20, 3*10=30, so output is [10, 20, 30].

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   Multiply each cell by 10 = [10, 20, 30] [OK]

Hint: Multiply each cell value inside ARRAYFORMULA [OK]

Common Mistakes:

• Expecting a single value instead of array output
• Confusing range size or output length
• Thinking it returns an error for valid ranges

4. You wrote =ARRAYFORMULA(A1:A + B1:B) but get an error. What is the likely cause?

medium

A. Formula needs to be entered in multiple cells

B. ARRAYFORMULA cannot add ranges

C. You must use SUM instead of +

D. Ranges A1:A and B1:B are not the same size

Solution

1. Step 1: Check range sizes in the formula

   ARRAYFORMULA requires ranges to be the same size to add element-wise. A1:A and B1:B are open-ended ranges but must cover same rows.

2. Step 2: Identify cause of error

   If one range is longer or shorter, Google Sheets throws an error because it can't match cells one-to-one.

3. Final Answer:

   Ranges A1:A and B1:B are not the same size -> Option D

4. Quick Check:

   Matching range sizes needed for element-wise addition [OK]

Hint: Ensure ranges have equal length for element-wise operations [OK]

Common Mistakes:

• Thinking ARRAYFORMULA can't add ranges
• Using SUM instead of element-wise +
• Entering formula in multiple cells instead of one

5. You have a list of prices in column A and quantities in column B. Which formula correctly calculates total sales for each row using ARRAYFORMULA?

hard

A. =ARRAYFORMULA(PRODUCT(A2:A, B2:B))

B. =ARRAYFORMULA(SUM(A2:A * B2:B))

C. =ARRAYFORMULA(A2:A * B2:B)

D. =ARRAYFORMULA(A2:A + B2:B)

Solution

1. Step 1: Understand the goal

   We want to multiply price by quantity for each row to get total sales per row.

2. Step 2: Analyze each option

   =ARRAYFORMULA(A2:A * B2:B) multiplies each price by quantity element-wise, which is correct. =ARRAYFORMULA(SUM(A2:A * B2:B)) sums all products into one value, not per row. =ARRAYFORMULA(A2:A + B2:B) adds instead of multiplies. =ARRAYFORMULA(PRODUCT(A2:A, B2:B)) uses PRODUCT which multiplies all values together, not element-wise.

3. Final Answer:

   =ARRAYFORMULA(A2:A * B2:B) -> Option C

4. Quick Check:

   Element-wise multiplication for totals = =ARRAYFORMULA(A2:A * B2:B) [OK]

Hint: Use element-wise * inside ARRAYFORMULA for row totals [OK]

Common Mistakes:

• Using SUM inside ARRAYFORMULA for per-row totals
• Adding instead of multiplying values
• Using PRODUCT which multiplies all values together