AVERAGE function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the average of a range with some empty cells

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

A 30

B 26

C 24

D 20

💡 Hint

AVERAGE ignores empty cells and calculates the mean of the numbers present.

The formula calculates the average of 10, 20, 40, and 50. Empty cells are ignored. So, (10 + 20 + 40 + 50) / 4 = 120 / 4 = 30.

❓ Function Choice

intermediate

Choose the correct formula to average only positive numbers

You have numbers in B1:B6: -5, 10, 15, -3, 20, 0. Which formula calculates the average of only the positive numbers?

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

B =SUM(B1:B6)/COUNT(B1:B6)

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

D =AVERAGE(B1:B6)

💡 Hint

Use AVERAGEIF to apply a condition to the average calculation.

AVERAGEIF with condition ">0" averages only positive numbers: 10, 15, and 20. So, (10 + 15 + 20) / 3 = 15.

📊 Formula Result

advanced

Average ignoring zeros in a range

Cells C1 to C5 contain: 5, 0, 15, 0, 25. What is the result of =AVERAGEIF(C1:C5, "<>0")?

A 9

B 15

C 10

D 45

💡 Hint

AVERAGEIF excludes zeros with the condition "<>0".

The formula averages only 5, 15, and 25. Sum is 45, count is 3, so average is 15.

🎯 Scenario

advanced

Calculate average of last 3 entries in a column

Column D has numbers in D1 to D6: 10, 20, 30, 40, 50, 60. You want to calculate the average of the last 3 numbers (40, 50, 60) using a formula. Which formula will do this correctly?

A =AVERAGE(OFFSET(D6,-2,0,3,1))

B =AVERAGE(D4:D6)

C =AVERAGE(D3:D5)

D =AVERAGE(D1:D3)

💡 Hint

OFFSET can create a dynamic range starting 2 rows above D6 with height 3.

OFFSET(D6,-2,0,3,1) creates a range from D4 to D6. Averaging this gives the average of last 3 entries.

❓ data_analysis

expert

Identify the average after filtering out errors

Cells E1 to E5 contain: 10, #DIV/0!, 20, #N/A, 30. Which formula correctly calculates the average ignoring error values?

A =AVERAGE(E1:E5)

B =AVERAGEIF(E1:E5, ">0")

C =SUM(E1:E5)/COUNT(E1:E5)

D =AGGREGATE(1, 6, E1:E5)

💡 Hint

AGGREGATE with function 1 (AVERAGE) and option 6 ignores errors.

AVERAGE and SUM/COUNT will error because of error cells. AVERAGEIF ignores errors but also ignores zeros and negatives. AGGREGATE(1,6,range) calculates average ignoring errors only.