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.

Practice

1. What does the AVERAGE function do in Excel?

easy

A. It adds all numbers without dividing.

B. It adds numbers and divides by how many numbers there are.

C. It counts how many cells have numbers.

D. It finds the highest number in a range.

Solution

1. Step 1: Understand the purpose of AVERAGE

   The AVERAGE function calculates the sum of numbers and divides by the count of those numbers.

2. Step 2: Compare options with definition

   Only It adds numbers and divides by how many numbers there are. correctly describes this behavior.

3. Final Answer:

   It adds numbers and divides by how many numbers there are. -> Option B

4. Quick Check:

   AVERAGE = sum ÷ count [OK]

Hint: AVERAGE means sum of numbers divided by count [OK]

Common Mistakes:

• Thinking AVERAGE finds the highest number
• Confusing AVERAGE with COUNT
• Believing AVERAGE just adds numbers

2. Which of these is the correct way to write the AVERAGE function for cells A1 to A5?

easy

A. =AVERAGE(A1:A5)

B. =AVERAGE[A1:A5]

C. =AVERAGE{A1:A5}

D. =AVERAGE A1:A5

Solution

1. Step 1: Recall correct Excel function syntax

   Functions use parentheses () around arguments, and ranges use colon : between cells.

2. Step 2: Check each option

   Only =AVERAGE(A1:A5) uses parentheses and colon correctly.

3. Final Answer:

   =AVERAGE(A1:A5) -> Option A

4. Quick Check:

   Correct syntax uses parentheses and colon [OK]

Hint: Use parentheses and colon for ranges in functions [OK]

Common Mistakes:

• Using square brackets instead of parentheses
• Using curly braces incorrectly
• Omitting parentheses around arguments

3. Given the values in cells A1=10, A2=20, A3=, A4="text", A5=30, what is the result of =AVERAGE(A1:A5)?

medium

A. 15

B. 25

C. 60

D. 20

Solution

1. Step 1: Identify numeric values in range

   Cells A1=10, A2=20, A5=30 are numbers; A3 is empty, A4 is text (ignored).

2. Step 2: Calculate average of numbers

   Sum = 10 + 20 + 30 = 60; Count = 3; Average = 60 ÷ 3 = 20.

3. Final Answer:

   20 -> Option D

4. Quick Check:

   Sum 60 ÷ 3 numbers = 20 [OK]

Hint: AVERAGE ignores empty cells and text [OK]

Common Mistakes:

• Including empty cells in count
• Including text as zero
• Adding all cells regardless of content

4. You wrote =AVERAGE(A1;A5) but get an error. What is the problem?

medium

A. AVERAGE cannot use cell references.

B. Parentheses are missing.

C. Semicolon should be a colon for range.

D. Function name is misspelled.

Solution

1. Step 1: Understand range syntax in Excel

   Ranges use colon ':' between start and end cells, not semicolon ';'.

2. Step 2: Identify error cause

   Using semicolon causes Excel to treat arguments separately, not as a range.

3. Final Answer:

   Semicolon should be a colon for range. -> Option C

4. Quick Check:

   Use ':' for ranges, not ';' [OK]

Hint: Use colon ':' to specify cell ranges [OK]

Common Mistakes:

• Using semicolon instead of colon for ranges
• Thinking AVERAGE can't use cell references
• Missing parentheses around arguments

5. You have sales data in cells B2:B10, but some cells contain text notes. Which formula correctly calculates the average sales ignoring text?

hard

A. =SUM(B2:B10)/COUNT(B2:B10)

B. =SUM(B2:B10)/COUNTA(B2:B10)

C. =AVERAGEIF(B2:B10,">0")

D. =SUM(B2:B10)/9

Solution

1. Step 1: Identify how to ignore text in average

   SUM ignores text and sums only numbers. COUNT ignores text and counts only numbers. So SUM/COUNT gives average of numbers only.

2. Step 2: Check =SUM(B2:B10)/COUNT(B2:B10)

   =SUM(B2:B10)/COUNT(B2:B10) uses exactly this approach.

3. Step 3: Why others fail

   B: COUNTA counts text cells too, wrong denominator. C: AVERAGEIF >0 excludes zero sales. D: /9 assumes all 9 cells numeric, but text present, wrong.

4. Final Answer:

   =SUM(B2:B10)/COUNT(B2:B10) -> Option A

5. Quick Check:

   SUM ÷ COUNT ignores text correctly [OK]

Hint: Use COUNT to count numbers only, ignoring text [OK]

Common Mistakes:

• Using COUNTA which counts text too
• Using AVERAGEIF without correct criteria
• Dividing by total cells instead of number count