Why array formulas process ranges at once in Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum the range A1:A5 using an array formula.

Google Sheets

=ARRAYFORMULA(SUM([1]))

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

AA1

BA1:A5

CSUM(A1:A5)

DA1:A5:A10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a single cell reference instead of a range.

Putting a function like SUM inside the range argument.

Using an invalid range syntax like A1:A5:A10.

The range A1:A5 correctly specifies the cells to sum inside the ARRAYFORMULA. This allows the formula to process all values at once.

2fill in blank

medium

Complete the formula to multiply each value in B1:B3 by 2 using ARRAYFORMULA.

Google Sheets

=ARRAYFORMULA([1] * 2)

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

AB1:B3

BB1

C2

DSUM(B1:B3)

Attempts:

3 left

💡 Hint

Common Mistakes

Using a single cell reference instead of a range.

Using a number instead of a range.

Using SUM which returns a single value, not a range.

The range B1:B3 allows ARRAYFORMULA to multiply each cell in the range by 2 at once.

3fill in blank

hard

Fix the error in this formula that tries to add 5 to each value in C1:C4: =ARRAYFORMULA(C1:C4 {{BLANK_1}} 5)

Google Sheets

=ARRAYFORMULA(C1:C4 [1] 5)

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

A+

B&

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using & which concatenates text instead of adding numbers.

Using * or - which multiply or subtract instead of adding.

The plus sign + correctly adds 5 to each value in the range C1:C4 using ARRAYFORMULA.

4fill in blank

hard

Fill both blanks to create a formula that returns TRUE for values in D1:D5 greater than 10 using ARRAYFORMULA.

Google Sheets

=ARRAYFORMULA(D1:D5 [1] [2])

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

A>

B<

C10

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > for greater than.

Using the wrong number to compare.

The operator > checks if values are greater than the number 10. Using these inside ARRAYFORMULA processes the whole range at once.

5fill in blank

hard

Fill all three blanks to create a formula that returns the length of each word in E1:E4 if the length is greater than 3 using ARRAYFORMULA and IF.

Google Sheets

=ARRAYFORMULA(IF(LEN(E1:E4) [1] [2], LEN([3]), ""))

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

A>

B3

CE1:E4

DLEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of >.

Using a wrong number for comparison.

Putting LEN inside LEN incorrectly.

The formula checks if the length of each word in E1:E4 is greater than 3. If true, it returns the length; otherwise, it returns an empty string. This works for all cells at once using ARRAYFORMULA.