How to Use AVERAGE Function in Excel (5 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel provides several statistical functions to help you perform tasks easily and swiftly. Today we are going to show you how to use a statistical function- the AVERAGE function in Excel. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

How to Use AVERAGE Function in Excel


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Introduction to Excel AVERAGE Function

The AVERAGE function is categorized under the Statistical functions in Excel. This function returns the average value of a given argument

Summary:

Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.

Syntax:

AVERAGE (number1, [number2], ...)

Arguments:

Argument Required/Optional Explanation
number1 Required This is the first number, cell reference, or range for which we want the average.
number2 Optional Additional numbers, cell references, or ranges for which you want the average, up to a maximum of 255.

Versions:

Workable from Excel 2003.


5 Suitable Examples to Use AVERAGE Function in Excel

Now in this section, we’ll demonstrate 5 simple examples to learn how to use the AVERAGE function in Excel.

Example 1: Basic Use of AVERAGE Function

From the description of the AVERAGE function, you might have understood that the basic use of this function is to find the average of several numbers.

To show examples, we have brought a simple dataset of five students and their respective scores in three tests. Now we’ll find the average score for each student. And to do that, all we need to insert the scores within the AVERAGE function.

Steps:

  • Insert the following formula in Cell F5 and hit the Enter button-
=AVERAGE(C5:E5)

Here we have provided the scores within the function using Cell Reference.

Basic Use of AVERAGE Function

This formula worked perfectly and provided the average score of student Maria. 

  • Using a similar formula (changing the cell reference) or using the Fill Handle tool, we can find the average for the rest of the students.


Use AVERAGE Function in Columns:

The AVERAGE function can be worked within the columns.

To show you examples, we have introduced a dataset of several scorers and their scored goals in their respective leagues. We will find the average goal involvement of the players.

Steps:

  • To find the average for the first player (Ronaldo), then our formula in Cell C8 will be-
=AVERAGE(C5:C6)

Use AVERAGE in Columns

  • We have found the average goal involvement of Ronaldo. Using the Fill Handle tool will produce the result for the rest of the players.


Example 2: Find the Average Percentages

What if you need to find the average of a percentage number? No worries you can do that easily by using the AVERAGE function.

Our example dataset has the percentage of the score of several students. We’ll find the average score percentage.

Steps:

  • Type the following formula in Cell C11 and press the Enter button to get the output-
=AVERAGE(C5:C9)

The C5:C9 range contains the Score Percentage.

Find the Average of Percentage

We have found the average of these percentage values.


Example 3: Find the Average Time

The AVERAGE function can calculate the average time. Average time for different units (hour or minute or second) may seem an easy task, but what for a time value that includes hour, minute, and second? Though it sounds hard, that is also can be managed easily using the AVERAGE function.

We have introduced a dataset of five marathon racers and their race-finishing time. The time is formatted as h:mm: ss.

Steps:

  • To calculate the average time, we are going to use the formula written below in Cell C11
=AVERAGE(C5:C9)

Find the Average of Time

The formula has produced the average time and the format remains as the source time.


Example 4: Find the Average of the Top N Values

Circumstances may arise where you need to find the average of n number of top values. Here n can be any real number.

Here we have the dataset of students and their test scores. We will find the average of the top 3 values. We are aiming to find the average of the top 3 values, so we will use a combination of the  LARGE  and AVERAGE functions that will provide the highest 3 numbers and then find their average.

Steps:

  • Insert the following formula in Cell H5
=AVERAGE(LARGE(C5:G5,{1,2,3}))

Find the Average of the Top N Values

Formula Breakdown:

  • LARGE(C5:G5,{1,2,3})

Here within the LARGE function, we are asking for more than one value by passing an array constant {1,2,3} as the second argument, and this causes LARGE to return an array result that includes the highest 3 values.

  • AVERAGE(LARGE(C5:G5,{1,2,3}))

And then the AVERAGE function will generate the average for the highest three values.

  • Next, apply the Fill Handle tool to copy the formula for the other cells

Note: If you need to find the average of n number of least values you can use SMALL in place of LARGE.


Example 5: Find Average Ignoring 0

Sometimes the dataset can contain zero but you may want to find the average by ignoring zero. Then you can combine the IF function with the AVERAGE function in a formula. We inserted 0 in Cell D5 to show it.

Steps:

  • Type the following formula in Cell F5
=AVERAGE(IF(C5:E5<>0,C5:E5))
  • Then press the Enter button and you will get the average ignoring zero.

How to Ignore 0 in Excel AVERAGE Function

Formula Breakdown:

  • IF(C5:E5<>0,C5:E5)

The IF function will return the range excluding zero.

  • AVERAGE(IF(C5:E5<>0,C5:E5))

And then the AVERAGE function will calculate the average for the range.


Similar Readings


How to Solve When Excel AVERAGE Function Is Not Working

Here see, the average function is not working in row 9. It’s returning a #DIV/0! error.

AVERAGE Function Is Not Working in Excel

The reason is the numbers in row 9 are stored in Text format. That’s why the AVERAGE function is not working properly.

Chnage Format When AVERAGE Function Is Not Working in Excel

Solution:

  • Store the numbers in Number format.
  • Or to convert them, select the numbers and then click on the error icon.
  • Later, select the Convert to Number option from the appeared menu.

Use Convert to Number Option When AVERAGE Function Is Not Working in Excel

Now it’s working.


Quick Notes

  • You can provide numbers directly alongside the cell reference.

Here we have inserted 9 with the cell reference of the numbers- 10,9,10,7.

We have found the average of numbers in cells B5 through B8, and the number 9.

  • Apart from the range, we can set the cell references separating by comma (,). Inside the function, we have set the references and they are separated by commas.

  • Your provided range within the AVERAGE function can have empty cells.

Here we have provided B5 to B9 and B6, B8 are empty cells. The AVERAGE function will ignore the empty cell and calculate the average for the rest of the cells.

  • One of the major reasons for returning the #DIV/0! error while using the AVERAGE function is, selecting text values instead of numbers.

AVERAGE Function in Excel: Knowledge Hub

Conclusion

That’s all for today. We have tried showing you how you can use the AVERAGE function. You can use the function to calculate the average value from ranges as well as from different formats. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your AVERAGE function-related scenarios where you have stuck, we are ready to help.


Further Readings

Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo