The AVERAGE function in Excel is one of the most commonly used functions.
In this tutorial, you will learn how to use the AVERAGE function. We’ll also show you how to use it with multiple number ranges.
Here’s what we’ll cover:
- What is the AVERAGE function and what is it used for?
- How to use the AVERAGE function in Excel (step-by-step)
- Things to consider when using the AVERAGE function
- Using multiple number ranges
1. What is the AVERAGE function and why use it?
The AVERAGE function returns the average from a given range of numbers.
This is a useful statistical function and can be used for many reasons. For example, to return the average sales total, the average exam score of students, or maybe the average monthly expense on food.
The average returned is the arithmetic mean.
This is calculated by adding a range of numbers and then dividing by the count of those numbers. However, you do not need to know this, because the AVERAGE function does it for you.
The AVERAGE function can handle multiple number ranges, if required. This is its syntax;
=AVERAGE(number1, number2, …)
2. How to use the AVERAGE function
There are two main ways to use the AVERAGE function, and both are covered in this tutorial. Download this workbook to follow along.
We have the following list of exam scores by students and we need to return the average exam score into cell D3.
1. Click in cell D3, as that is where we want the result returned.
2. Click Home, click the arrow next to the AutoSum button and then click Average.
3. The AVERAGE function attempts to find the range of numbers to use. In this example, it asks if we want to use B3:C3, which is incorrect.
This range is highlighted gray, as it is ready for us to select the correct range to use.
4. Select range B2:B8. As you do so, this replaces the initial range.
5. Press Enter.
The average result is returned. This result has four decimal places.
Let’s remove the decimals as they are not required in this example.
1. Select cell D3.
2. Click Home and then click the Decrease Decimal button four times (once for each decimal place).
There are two decimal buttons on the Ribbon. The first increases the number of decimals and the second one decreases them.
The result is now shown to 0 decimal places.
Instead of using the button on the Ribbon, you can write the formula into the cell. This is the method many regular Excel users would take.
1. Click in cell D3.
2. Type = and then average. As you type, a list of functions appears.
3. You can double click the AVERAGE function in this list to complete it, or keep typing. If you type it, remember to type an open parenthesis “(“ after the function name.
4. Select range B2:B8. Type a closing parenthesis “)” and press Enter.
3. Things to consider when using the AVERAGE function
There are a few things to consider when using the AVERAGE function:
- The range of numbers can be provided as a range, defined name, or a table reference.
- The AVERAGE function will ignore text and blank cells in the range.
- Cells that contain 0 are included in the average result. To exclude them, the AVERAGEIF function can be used.
Related watching: How to create an AVERAGEIF statement
4. Using multiple number ranges
The AVERAGE function can handle multiple ranges of numbers, if required.
In this example, we have two lists of student exam scores. We want to return the average into cell G3, for all student exam scores from both regions.
1. Click in cell G3.
2. Type “=AVERAGE(“ and select range B3:B9.
3. Type a comma “,”. This is used to separate the two number ranges (this is shown in the tooltip provided).
4. Select range E3:E11. Type a closing parenthesis “)” and press Enter.
The average is returned for all student exam scores.
When calculating the average from multiple ranges, you can also hold the Ctrl key and select another range instead of typing the comma separator.
The AVERAGE function can handle up to 255 ranges. More than you will ever need!
If you’re getting to grips with Excel in your quest to become a data analyst, you can try a free data analytics short course here. Keen to learn more Excel functions? Check out the following: