AVERAGE function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the average of numbers in cells A1 to A5.

Excel

=AVERAGE([1])

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

AA1;A5

BA1,A5

CA1-A5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) instead of a colon (:)

Separating cells with a semicolon (;) or comma (,)

The correct way to specify a range in Excel is with a colon (:). So A1:A5 means all cells from A1 through A5.

2fill in blank

medium

Complete the formula to calculate the average of cells B2, B3, and B4.

Excel

=AVERAGE([1])

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

AB2-B4

BB2:B4

CB2,B3,B4

DB2;B3;B4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a colon when listing individual cells

Using semicolons instead of commas

When listing individual cells, separate them with commas inside the AVERAGE function, like B2,B3,B4.

3fill in blank

hard

Fix the error in the formula to correctly calculate the average of cells C1 to C3.

Excel

=AVERAGE(C1[1]C3)

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

A:

B,

C-

D;

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) instead of a colon (:)

Using a semicolon (;) or comma (,) instead of a colon

The correct range separator in Excel formulas is a colon (:), so C1:C3 selects cells C1 through C3.

4fill in blank

hard

Fill the blank to calculate the average of cells D1 to D4 and ignore empty cells.

Excel

=AVERAGE([1])

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

AD1-D4

BD1:D4

CD1,D4

DD1;D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash (-) or semicolon (;) instead of a colon (:)

Separating cells with commas when a range is needed

The correct way to specify the range is D1:D4. Empty cells are ignored automatically by AVERAGE.

5fill in blank

hard

Fill all three blanks to calculate the average of cells E1, E3, and E5.

Excel

=AVERAGE([1],[2],[3])

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

AE1

BE3

CE5

DE2

Attempts:

3 left

💡 Hint

Common Mistakes

Including cells not intended (like E2)

Using semicolons or other separators instead of commas

To average specific cells, list each cell separated by commas inside the AVERAGE function.

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