AVERAGE function in Google Sheets - Step-by-Step Guide

Introduction

The AVERAGE function helps you find the middle value of a group of numbers. It adds all the numbers together and then divides by how many numbers there are. This is useful when you want to know the typical value in a list, like average test scores or daily sales.

When you want to find the average score of students in a class.

When you need to calculate the average daily temperature for a week.

When you want to know the average sales amount from a list of transactions.

When you want to find the average time spent on a task from several records.

When you want to summarize the average rating from customer feedback scores.

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 cell range is highlighted and added to the formula

Step 4: Type

- the formula input

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

Function name

📍 Formula bar when typing a formula

Specifies the function to calculate the average of numbers

Default: AVERAGE

Cell range

📍 Inside the parentheses of the formula

Defines which numbers to include in the average calculation

Default: None

Common Mistakes

Typing =AVERAGE without selecting any cells or numbers

The formula has no numbers to calculate, so it returns an error or zero.

Always include a range or list of numbers inside the parentheses, like =AVERAGE(A1:A5).

Including text or empty cells in the range

Text and empty cells are ignored, which might give unexpected results if you expect them to count as zero.

Make sure the range only includes cells with numbers or handle text separately.

Summary

The AVERAGE function calculates the middle value of a group of numbers by adding them and dividing by the count.

You must select the cells with numbers inside the parentheses for the function to work.

Text and empty cells in the range are ignored and do not affect the average.

Practice

1. What does the AVERAGE function do in Google Sheets?

easy

A. It adds all numbers and divides by the count of numbers.

B. It finds the biggest number in a list.

C. It counts how many numbers are in a list.

D. It multiplies all numbers together.

Solution

1. Step 1: Understand the purpose of AVERAGE

   The AVERAGE function sums all numeric values in a range and divides by how many numbers there are.

2. Step 2: Compare options with definition

   Only It adds all numbers and divides by the count of numbers. correctly describes this behavior.

3. Final Answer:

   It adds all numbers and divides by the count of numbers. -> Option A

4. Quick Check:

   AVERAGE = sum ÷ count [OK]

Hint: AVERAGE means sum numbers then divide by count [OK]

Common Mistakes:

• Thinking AVERAGE finds the biggest number
• Confusing AVERAGE with COUNT
• Assuming it multiplies numbers

2. Which of these is the correct way to write the AVERAGE formula 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 formula syntax

   Google Sheets formulas use parentheses () to enclose cell ranges or arguments.

2. Step 2: Check each option

   Only =AVERAGE(A1:A5) uses parentheses correctly: =AVERAGE(A1:A5). Others use brackets, braces, or no parentheses, which are invalid.

3. Final Answer:

   =AVERAGE(A1:A5) -> Option C

4. Quick Check:

   Formulas need parentheses () [OK]

Hint: Use parentheses () for function arguments [OK]

Common Mistakes:

• Using square brackets [] instead of parentheses
• Using curly braces {} incorrectly
• Omitting parentheses

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. Error

B. 15

C. 25

D. 20

Solution

1. Step 1: Identify numeric values in range

   Cells A1=10, A2=20, A5=30 are numbers; A3 is empty and A4 is text, both ignored by AVERAGE.

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:

   Ignore empty/text, average numbers only [OK]

Hint: AVERAGE ignores empty cells and text [OK]

Common Mistakes:

• Including empty cells in count
• Including text as zero
• Expecting error due to text

4. The formula =AVERAGE(A1:A4, 10) returns an error. What is the likely cause?

medium

A. The syntax is incorrect; extra parentheses needed.

B. The formula is correct; error is elsewhere.

C. You cannot mix ranges and single numbers in AVERAGE.

D. You must separate arguments with semicolons, not commas.

Solution

1. Step 1: Verify formula syntax

   =AVERAGE(A1:A4, 10) is valid in Google Sheets; AVERAGE can take a range and a constant value as arguments.

2. Step 2: Determine error source

   Since the syntax is correct, the error must originate elsewhere, such as invalid data in A1:A4, cell references, or spreadsheet settings.

3. Final Answer:

   The formula is correct; error is elsewhere. -> Option B

4. Quick Check:

   AVERAGE(range, number) is valid [OK]

Hint: AVERAGE accepts ranges + numbers; check data/range [OK]

Common Mistakes:

• Assuming mixing ranges and numbers is invalid
• Using wrong argument separators
• Missing equal sign at start

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. =AVERAGE(B2:B10)

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

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

D. =AVERAGE(B2:B10, "text")

Solution

1. Step 1: Understand how AVERAGE handles text

   AVERAGE automatically ignores text and empty cells when calculating the average.

2. Step 2: Evaluate each option

   =AVERAGE(B2:B10) uses AVERAGE directly, which works correctly. =SUM(B2:B10)/COUNTA(B2:B10) counts text cells, causing wrong divisor. =AVERAGEIF(B2:B10, ">0") averages only numbers >0, excluding zeros. =AVERAGE(B2:B10, "text") includes text as argument, causing error.

3. Final Answer:

   =AVERAGE(B2:B10) -> Option A

4. Quick Check:

   AVERAGE ignores text automatically [OK]

Hint: Use AVERAGE directly; it skips text cells [OK]

Common Mistakes:

• Using COUNTA which counts text too
• Trying to filter numbers manually
• Adding text as argument causing errors