Bird
Raised Fist0
Excelspreadsheet~5 mins

AVERAGE function in Excel - Step-by-Step Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Introduction
The AVERAGE function calculates the mean value of a group of numbers. It helps you find the typical value in a list, like the average score or average price, without doing the math yourself.
When you want to find the average test score of a class from a list of scores.
When you need to calculate the average sales amount from daily sales data.
When you want to know the average temperature over a week from recorded daily temperatures.
When you want to find the average price of items in a shopping list.
When you want to quickly summarize the average time spent on tasks from a time log.
Steps
Step 1: Click
- the cell where you want the average result to appear
The cell is selected and ready for input
Step 2: Type
- the selected cell
The formula input starts in the cell
💡 Start by typing =AVERAGE(
Step 3: Select
- the range of cells containing the numbers you want to average
The selected cells are highlighted and their addresses appear in the formula
Step 4: Type
- the formula bar or cell
The formula is completed
💡 Close the formula with a closing parenthesis )
Step 5: Press
- Enter key
The cell shows the average of the selected numbers
Before vs After
Before
Cells A1 to A5 contain numbers: 10, 20, 30, 40, 50. Cell B1 is empty.
After
Cell B1 shows 30, which is the average of the numbers in A1 to A5.
Settings Reference
Number format
📍 Home tab > Number group
Controls how the average result is displayed, such as decimal places or currency symbol
Default: General
Common Mistakes
Typing the formula without parentheses, like =AVERAGE
Excel needs parentheses to know which cells to average.
Always include parentheses with the cell range inside, like =AVERAGE(A1:A5)
Including text cells in the range
Text cells are ignored, but if all cells are text, the result will be an error.
Select only cells with numbers or ensure at least one number is in the range.
Summary
The AVERAGE function finds the mean value of numbers in a selected range.
Use it to quickly calculate typical values like average scores or prices.
Remember to include parentheses and select only number cells for accurate results.

Practice

(1/5)
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