AVERAGE function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate average ignoring empty cells

Given the values in cells A1 to A5 as 10, 20, , 40, 50 (where the third cell is empty), what is the result of the formula =AVERAGE(A1:A5)?

A 24

B 25

C 30

D 20

💡 Hint

Remember that empty cells are ignored in AVERAGE calculation.

AVERAGE sums the numbers 10 + 20 + 40 + 50 = 120 and divides by 4 (non-empty cells), so 120/4 = 30.

❓ Function Choice

intermediate

Choose the correct formula to average only positive numbers

You have numbers in B1:B6: 5, -3, 8, 0, -1, 7. Which formula correctly calculates the average of only the positive numbers?

A =AVERAGEIF(B1:B6, "<0")

B =AVERAGE(B1:B6>0)

C =AVERAGE(B1:B6, ">0")

D =AVERAGEIF(B1:B6, ">0")

💡 Hint

Use AVERAGEIF to filter values based on a condition.

AVERAGEIF with ">0" averages only numbers greater than zero, ignoring negatives and zero.

📊 Formula Result

advanced

AVERAGE with mixed data types

Cells C1 to C5 contain: 10, "text", 20, TRUE, 30. What is the result of =AVERAGE(C1:C5)?

A 20

B 15

C Error

D 25

💡 Hint

AVERAGE ignores text and logical values unless they are numbers.

Only numbers 10, 20, and 30 are counted. Sum is 60, count is 3, average is 20.

🎯 Scenario

advanced

Calculate average excluding zeros

You want to find the average of values in D1:D7 but exclude zeros from the calculation. Which formula should you use?

A =AVERAGE(D1:D7, "<>0")

B =AVERAGEIF(D1:D7, "<>0")

C =AVERAGEIF(D1:D7, "=0")

D =AVERAGE(D1:D7)-0

💡 Hint

Use AVERAGEIF with a condition to exclude zeros.

AVERAGEIF with "<>0" averages only cells not equal to zero.

❓ data_analysis

expert

Find the average of last 3 entries in a growing list

Column E has a list of numbers starting from E1 downwards. The list grows over time. Which formula calculates the average of the last 3 numeric entries in column E, assuming no blanks between them?

A =AVERAGE(OFFSET(E1, COUNTA(E:E)-3, 0, 3, 1))

B =AVERAGE(E1:E3)

C =AVERAGE(OFFSET(E1, COUNTA(E:E), 0, 3, 1))

D =AVERAGE(E:INDEX(E:E, COUNTA(E:E)-3))

💡 Hint

Use OFFSET with COUNTA to dynamically select last 3 cells.

COUNTA counts entries, OFFSET moves down to last 3 entries, AVERAGE calculates their mean.