Sheets API basics in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula?

Given the following data in cells A1:A5: 2, 4, 6, 8, 10, what is the output of the formula =SUM(A1:A5)/COUNT(A1:A5)?

Google Sheets

=SUM(A1:A5)/COUNT(A1:A5)

A 30

B 6

C 5

D 10

💡 Hint

Think about what SUM and COUNT functions do and how dividing them gives the average.

SUM adds all numbers: 2+4+6+8+10=30. COUNT counts how many numbers: 5. Dividing 30 by 5 gives 6.

❓ Function Choice

intermediate

Which function counts only non-empty cells?

You want to count how many cells in range B1:B10 contain any data (numbers or text). Which function should you use?

A COUNTA(B1:B10)

B COUNTBLANK(B1:B10)

C SUM(B1:B10)

D COUNT(B1:B10)

💡 Hint

COUNT counts only numbers, but you want to count all non-empty cells.

COUNTA counts all non-empty cells including text and numbers. COUNT counts only numbers.

🎯 Scenario

advanced

How to sum values only if another column meets a condition?

You have sales amounts in column C and regions in column B. You want to sum sales only for region "East". Which formula will do this correctly?

A =SUMIFS(C:C, B:B, "East")

B =SUM(C:C, IF(B:B="East"))

C =SUMIF(B:B, "East", C:C)

D =SUMIF(C:C, "East", B:B)

💡 Hint

SUMIF sums values in one range based on criteria in another range.

SUMIF(range_to_check, criteria, range_to_sum) sums values in range_to_sum where range_to_check matches criteria.

📊 Formula Result

advanced

What is the output of this array formula?

Given cells A1:A3 contain 1, 2, 3 respectively, what is the output of =ARRAYFORMULA(A1:A3*2)?

Google Sheets

=ARRAYFORMULA(A1:A3*2)

A 2, 4, 6

B Error

C 1, 2, 3, 2, 4, 6

D 6

💡 Hint

ARRAYFORMULA applies the operation to each element in the range.

Each value in A1:A3 is multiplied by 2, resulting in 2, 4, 6.

❓ data_analysis

expert

How many unique values are in this range?

Cells D1:D7 contain the values: 5, 3, 5, 7, 3, 9, 7. What is the result of =COUNTA(UNIQUE(D1:D7))?

Google Sheets

=COUNTA(UNIQUE(D1:D7))

A 5

B 7

C 3

D 4

💡 Hint

UNIQUE returns only distinct values, then COUNTA counts them.

Unique values are 5, 3, 7, 9. Counting them gives 4.