How To Calculate The Average In Excel:
Use The Function AVERAGE

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Finding averages is often required when working with spreadsheets.

It’s a rather basic task—but Excel can do all sorts of great things with averages.

In this tutorial, we’ll take a look at the AVERAGE function, as well as its sister functions and the cool things you can do with them.

Kasper Langmann, Co-founder of Spreadsheeto

If you think that AVERAGE is a simple function, with little utility, think again 😉

An introduction to the AVERAGE function

Excel’s AVERAGE function calculates the arithmetic mean of a set of numbers.

The arithmetic mean is usually what people think of when they think of an average. It’s the sum of the values divided by the number of values.

The average of 5, 10, and 15 is 10 (5+10+15 = 30 . . . 30/3 = 10).

Let’s take a quick look at the syntax:

The syntax of the AVERAGE function

=AVERAGE(number1, [number2]…)

It’s as simple as it looks.

Number1 is a number (like “547”) or a cell reference (like “B6”). So is number2. You can include as many numbers as you like.

You can use this function to get the average of a row or column, an entire array, or cells that are nowhere near each other.

Now, we’ll get a little practice with the AVERAGE function. Grab the free sample file so you can follow along!

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, I’ll be referencing a few spreadsheets in an example workbook.

Just click the button below to download it so you can follow along!

Download the FREE Exercise File

Download exercise file
Download free exercise file

The basics of the AVERAGE function

To see how it works, let’s take a look at a quick AVERAGE tutorial.

On the first sheet of our sample workbook, you’ll see a list of names followed by the amount of money they owe. Let’s calculate the average of the amount owed by the first ten people.

First, we’ll type “Average Owed” in cell D2, so we know what this number represents. Then, click into F2.

Start by typing an equals sign, the word “AVERAGE,” and an open parenthesis:

Then, select the cells that will be averaged. Click and drag from cell B2 to cell B11:

Type a closing parenthesis, and hit Enter. We now have the average of the first ten amounts owed.

As you’ll see, Excel will add a notification to this cell (in Excel 2016, this is signified by a green corner).

Clicking on the cell and the exclamation point that pops up, we see “Formula omits adjacent cells.”

This is just Excel’s way of telling you that you didn’t select all of the cells that have numbers in them. In our case, that’s fine, because we weren’t trying to select everything.

Kasper Langmann, Co-founder of Spreadsheeto

Also, keep in mind that cells with no value will not be counted by the AVERAGE function.

If a cell has a zero in it, it will be included in the average. Blank cells won’t.

Using AVERAGEA and AVERAGEIF

The AVERAGE function has two sister functions: AVERAGEA and AVERAGEIF.

Let’s take a look at them.

AVERAGEA is simple: it’s exactly the same as AVERAGE, but it can also handle TRUE and FALSE values. TRUE is counted as 1, and FALSE is counted as 0.

AVERAGEIF is a bit different, and it has some awesome uses.

It allows you to average a subset of cells based on some criteria.

Want to know how to use AVERAGEIF like a pro? Here’s a great example.

On the second sheet of the example workbook, you’ll see a list of sales amounts, along with “YES” or “NO” values for whether they’ve been completed.

Let’s use AVERAGEIF to average only the sales that have been completed. Here’s the syntax you’ll need:

The syntax of the AVERAGEIF function

=AVERAGE(range, criteria, [average_range])

The range parameter is the group of cells to which the criteria will be applied. If you don’t include a value for average_range, it will also be the range from which the average is calculated.

Criteria contains the conditions that the cells need to meet to be included in the average. This can take any arguments that resolve to true or false, including greater than, less than, equals, and the other Excel arguments that you’re used to.

The average_range parameter is optional. If you include this parameter, the cells represented by this range will be averaged.

Let’s see what that looks like.

Click into cell D2, and type “Average of completed sales,” so we don’t forget what we’re calculating. Then, in G2, type “=AVERAGEIF(“:

Select cells B2 through B11 for the range (this is the group of cells that we’ll apply the criteria to).

Now we’ll add the criteria. To make sure that we only average the sales that have been completed, we’ll type “YES”.

This tells Excel to look for “YES” in column B (in the next step, we’ll tell Excel to include the corresponding value from column A in our average).

Finally, we’ll add the cells that will be averaged. In our case, that’s A2:A11. Click and select those.

Close off the formula and hit Enter.

Now, we have the average of the completed sales.

There’s another related function that I should mention here: AVERAGEIFS. This allows you to input multiple criteria to choose the cells that are averaged.

These work the same way as SUM and SUMIFS.

Kasper Langmann, Co-founder of Spreadsheeto

Calculating moving averages in Excel

A moving average takes the average of a subset of numbers to smooth out inconsistencies. It’s best to show this with an example.

On the third sheet of the example workbook, you’ll see a record of rainfall over the course of two weeks. We’ll calculate the moving average.

The moving average will calculate the average of the past three days of rain. So on May 7th, the moving average will include the rainfall on the 5th, 6th, and 7th.

We’ll be going through a moving average tutorial, but first, you’ll need the Data Analysis ToolPak add-in.

Kasper Langmann, Co-founder of Spreadsheeto

To enable this add-in, go to File > Options > Add-ins. At the bottom of the window, you’ll see Manage: Excel Add-ins. Click the Go… button next to it. In the resulting pop-up, make sure there’s a check next to Analysis ToolPak. Then click OK.

Now, in the Data tab in the Ribbon, you’ll see the Data Analysis button. Click it.

Select Moving Average, and click OK.

This will bring up a new window:

The Input Range contains the cells that you want to average.

Select cells A2:O2, and make sure that the Labels in First Row box is checked (if you don’t check that box, you’ll get an error for non-numeric data in the range).

For our purposes, we’ll set the Interval to 3 (this gives us the 3-day moving average).

And, finally, we’ll select B3:O3 as the output range; this is where Excel will place the average values.

Now, hit OK.

As you can see, the moving average for each day has been inserted into row 3.

Cells B2 and C2 contain errors, as they have no 3-day average; this is to be expected. Every other day, however, has a 3-day average.

Kasper Langmann, Co-founder of Spreadsheeto

Using SUMPRODUCT for weighted averages

A weighted average is the average of a series of numbers, each multiplied by a factor that indicates its importance.

On the fourth sheet of the example workbook, you’ll see a situation in which weighted average are used often: calculating school grades.

In our particular example, attendance counts for 10% of the grade, quizzes for 20%, homework for 20%, and tests for the remaining 50%. These percentages serve as the weights for our weighted average.

Calculating the weighted average is a three-step process:

  1. multiply each value by the associated percentage
  2. add the resulting sums
  3. divide by the total weight (which, in the case of percentages, should be 1).

While there’s no function that handles all of that, we can use SUMPRODUCT (the big brother of the SUM function) to do it.

Here’s the formula for doing that:  =SUMPRODUCT(B2:B5, C2:C5)/SUM(B2:B5)

Let’s break that down…

SUMPRODUCT multiplies the corresponding values in each array and adds them.

The expanded form of that calculation looks like this:

(B2 x C2) + (B3 x C3) + (B4 x C4) + (B5 x C5)

All of that is divided by the sum of B2:B5. In this case, that’s 1. In other cases, it may be something else.

Using the SUMPRODUCT formula above, you’ll see that the weighted average of the grades in our example sheet works out to 89.9.

Weighted averages can be used in a wide variety of situations, from grades to pricing calculations. And although Excel doesn’t have a specific function for calculating these averages, you can use the SUMPRODUCT function to get the same effect.

Kasper Langmann, Co-founder of Spreadsheeto

Update (July 26th, 2019): I’ve published a new in-depth tutorial about calculating weighted averages here.

Wrapping things up…

Averages might seem mundane, but Excel can do some pretty exciting things with them.

From averaging a subset of specific cells to using moving and weighted averages, Excel has a lot of great features for getting generalized information from a larger data set.

I hope this tutorial on Excel’s AVERAGE function—and a few related averaging methods—has been of great use to you. Now get out there and average arrays like a boss!

2019-07-26T06:57:27+00:00