How to use AVERAGE function in Excel (5 Examples)

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 called: AVERAGE. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

Overview - Excel AVERAGE function

Practice Workbook

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

AVERAGE Function

1. Basics of AVERAGE

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

Syntax - Excel AVERAGE function

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

number1: This is the first number, cell reference, or a 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

2. Use of AVERAGE

I. Basic Use of AVERAGE

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.

Basic use - Excel AVERAGE function

We are set to find the average score for each student. And to do that, all we need to insert the scores within the AVERAGE function.

For the first row of the table

=AVERAGE(100,90,93)  

We have set the numbers from the first row of the table.

Direct input - Excel AVERAGE function

We have found the average of the three test scores.

Setting the input directly may be time-consuming, tedious and there is always the risk for error. So, it’s better to practice working with Cell Reference. 

Then our formula will be

=AVERAGE(C4:E4) 

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

Formula with cell reference - Excel AVERAGE function

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

Using a similar formula (changing the cell reference) we can find the average for the rest of the students.

Basic use autofill - Excel AVERAGE function

II. Find the Average of Percentage

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

Our example dataset has the percentage of the score of several students.

Percentage data - Excel AVERAGE function

We will find the average score percentage. Our formula will be

=AVERAGE(C4:C8)

The C4:C8 range contains the Score Percentage.

Percentage average - Excel AVERAGE function

We have found the average of these percentage values.

III. Find the Average of 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 AVERAGE.

Time dataset - Excel AVERAGE function

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

To calculate the average we are going to use the formula written below

=AVERAGE(C4:C8)

Time Average - Excel AVERAGE function

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

IV. Use AVERAGE 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 respective leagues.

Column dataset - Excel AVERAGE function

We will find the average goals involvement of the players.

Let’s imagine want to find the average for the first player (Ronaldo), then our formula will be

=AVERAGE(C4:C5)

Column Average formula - Excel AVERAGE function

We have found the average goal involvement of Ronaldo. A similar formula will produce the result for the rest of the players.

Autofill column average - Excel AVERAGE function

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

Top 3 average dataset - Excel AVERAGE function

We are aiming to find the average of the top 3 values, so we will use a function that will provide the highest 3 numbers and then find their average.

Our formula will be a combination of LARGE and AVERAGE.

The LARGE function returns numeric values based on their position in a list when sorted by value. To know the function details, visit this LARGE function article.

The formula will be

=AVERAGE(LARGE(C4:G4,{1,2,3}))

Top 3 average formula - Excel AVERAGE function

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.

To observe the insides, write the LARGE function portion

LARGE insides - Excel AVERAGE function

And then press F9. 

LARGE insides - Excel AVERAGE function

You can see the 3 largest values from the range.

The LARGE function provided the top 3 values and the AVERAGE function generated the average.

Top 3 value average autofill- Excel AVERAGE function

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

3. Quick Notes

  1. You can provide numbers directly alongside the cell reference.

Number with cell reference - Excel AVERAGE function

Here we have inserted 5 with the cell reference of the numbers 8 to 10.

Number with cell reference result - Excel AVERAGE function

We have found the average of numbers in cells A1 through A3 and the number 5.

  1. Apart from the range, we can set the cell references separating by comma (,).

Cell references using comma - Excel AVERAGE function

Inside the function, we have set the references and they are separated by commas.

Cell references using comma result- Excel AVERAGE function

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

Empty cells in formula - Excel AVERAGE function

Here we have provided A7 to A10 and A6 is an empty cell. The AVERAGE function will ignore the empty cell and calculate for the rest of the cells.

Empty cells in formula results- Excel AVERAGE function

Conclusion

That’s all for today. We have tried showing 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

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

ExcelDemy
Logo