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".