AVERAGE Function in Excel (7 Examples)

If you want the arithmetic mean of a set of numbers in Excel, the AVERAGE function is the fastest way to get it. You hand it a range and it returns the mean in a single cell.

There is one thing worth knowing up front. AVERAGE ignores blank cells and text, but it still counts any cell that holds a zero, and that can quietly pull your result down.

AVERAGE returns a single value, but it works fine inside dynamic array formulas like =AVERAGE(FILTER(...)).

In this article I’ll show you how to use AVERAGE in Excel, from a basic mean to averaging non-contiguous cells, skipping errors, and getting a conditional average.

I’ll also show you how to get an average without typing a formula, and when MEDIAN or MODE gives you a better answer.

AVERAGE Syntax

Here is the syntax of the AVERAGE function:

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

• number1 – The first number, cell reference, or range you want to average. This one is required.
• [number2], … – Optional. Any additional numbers, references, or ranges. You can pass up to 255 arguments in total.

A quick note on how it treats a range: AVERAGE ignores blank cells, text, and logical values (TRUE/FALSE) inside a referenced range. Cells that hold a zero are counted. If the range contains an error value, AVERAGE returns that error.

Note that TRUE and FALSE typed directly into the formula (not in a cell) are counted as 1 and 0. So =AVERAGE(TRUE,3) returns 2.

If you want text counted as 0 and TRUE counted as 1, use the AVERAGEA function instead. It includes those cells in the count, while AVERAGE skips them.

When to Use AVERAGE Function

Use the AVERAGE function when you need to:

• Find the mean of a column or row of numbers in one cell.
• Average values sitting in cells that are not next to each other.
• Feed a mean into a bigger formula, like averaging only the rows a FILTER returns.

Let me show you a few practical examples of how AVERAGE works.

Example 1: Get a Basic Average

Let’s start with a simple example.

Below is a dataset with the length of a person’s daily workout, in minutes, for one week. Column A has the day and column B has the minutes.

I want the average workout length across the whole week.

Here is the formula:

=AVERAGE(B2:B8)

This returns 48, which is the average of the seven daily values. AVERAGE adds up everything in B2:B8 and divides by how many numbers it found, so you don’t have to count the cells yourself.

If you ever want to double-check the result, divide the SUM of the range by the COUNT of numbers in it:

=SUM(B2:B8)/COUNT(B2:B8)

This also returns 48 (336 divided by 7). It’s exactly what AVERAGE does in the background, and seeing the total and the count separately helps when a result looks off.

How to Get an Average in Excel Without Typing a Formula

If you just need the number and don’t want to type anything, Excel has two quick options. Both use the same workout data from Example 1.

The first is the Average option in the AutoSum drop-down, which writes the AVERAGE formula for you.

Here are the steps:

1. Select cell B9, the empty cell right below the workout minutes.

2. In the Home tab, click the small arrow next to AutoSum (in the Editing group).

3. Click Average.

4. Press Enter.

Excel inserts =AVERAGE(B2:B8) and returns 48. It guesses the range from the numbers right above the cell, so check the highlighted range before you press Enter.

The second option doesn’t put anything in a cell at all. Select B2:B8 and look at the status bar at the bottom of the Excel window.

It shows the Average, Count, and Sum of the selected cells right away. If you don’t see Average there, right-click the status bar and check Average.

Example 2: How AVERAGE Handles Blanks vs Zeros

This next one is the mistake I see trip people up the most.

Below is a dataset with the units a salesperson sold each day from Monday to Friday. On Wednesday and Friday the cells are blank, because those were days off with no data recorded.

I want the average units sold across the days the person actually worked.

Here is the formula:

=AVERAGE(B2:B6)

This returns 15. AVERAGE skips the two blank cells entirely, so it adds 12 + 15 + 18 and divides by 3, not by 5.

Now look at what happens when those two days hold a 0 instead of being blank. Same numbers on the working days, but Wednesday and Friday now show a real zero.

Here is the same formula on that version (the zeros table is in column G):

=AVERAGE(G2:G6)

This time it returns 9. AVERAGE counts the two zeros as real values, so it divides the same total of 45 by 5 days instead of 3. A blank and a zero are not the same thing to AVERAGE.

Pro Tip: If you want to ignore zeros as well as blanks, use AVERAGEIF with a “not equal to zero” condition: =AVERAGEIF(G2:G6,”<>0″). It averages only the non-zero numbers.

Example 3: Average Non-Contiguous Cells

Here’s a handy one when the cells you care about are scattered.

Below is a dataset with new sign-ups for each month of the year. Column A has the month and column B has the count.

You launched a new feature only in January, April, July, and October, and those are the rows you want to look at.

I want the average sign-ups for just those four launch months, ignoring every other row.

Here is the formula:

=AVERAGE(B2,B5,B8,B11)

This returns 165. Instead of a single range, you pass each cell as its own argument separated by commas, and AVERAGE means just those four. The values are 120, 160, 200, and 180, which add to 660 and divide by 4.

Example 4: Mix Ranges, Cells, and Typed Numbers

AVERAGE doesn’t care what kind of arguments you give it. You can mix a range, a single cell, and a number typed right into the formula.

Below is a dataset with a student’s scores on five quizzes in B2:B6. The midterm score sits on its own in cell E2.

I want the average of the five quizzes, the midterm, and a make-up quiz score of 85 that isn’t in the sheet yet.

Here is the formula:

=AVERAGE(B2:B6,E2,85)

This returns 83. AVERAGE sees seven numbers here: the five quiz scores, the 83 in E2, and the 85 you typed. They add up to 581, and 581 divided by 7 is 83.

Typing a number into the formula is handy for a quick what-if. If the value is going to stay, put it in a cell instead so it’s easy to see and change later.

Pro Tip: AVERAGE gives every value the same weight, so the midterm counts as much as one quiz. If it should count more, use a weighted average built with SUMPRODUCT instead.

Example 5: Average and Ignore Error Values

Let’s look at something that catches a lot of dashboards off guard.

Below is a dataset of stores showing their Sales, Visits, and the Sales per Visit ratio in column D (Sales divided by Visits). A couple of stores had no visits that day, so their ratio came out as a #DIV/0! error.

I want the average ratio across the stores that do have a valid number.

Here is the plain AVERAGE formula first:

=AVERAGE(D2:D7)

This returns #DIV/0!. A single error anywhere in the range makes AVERAGE hand that same error straight back, which is rarely what you want.

The same thing happens with any other error, like a #N/A from a lookup that didn’t find a match.

To skip the errors, use the AGGREGATE function instead:

=AGGREGATE(1,6,D2:D7)

This returns 4.2. In AGGREGATE, the first argument 1 tells it to average, and the second argument 6 tells it to ignore error values. So it averages only the four clean numbers and quietly drops the two errors.

Example 6: Average the Top (or Bottom) N Values

Now let’s combine AVERAGE with another function.

Below is a dataset with the monthly sales for eight reps. Column A has the rep name and column B has their sales number. You don’t want the average of everyone, just your strongest performers.

I want the average sales of the top three reps.

Here is the formula:

=AVERAGE(LARGE(B2:B9,{1,2,3}))

This returns 370. The LARGE function pulls the 1st, 2nd, and 3rd highest values (410, 360, and 340) as a small array, and AVERAGE takes the mean of just those three.

You can flip it to the bottom three by swapping in the SMALL function, which grabs the lowest values instead:

=AVERAGE(SMALL(B2:B9,{1,2,3}))

This returns about 183.3, the mean of the three lowest sales (150, 180, and 220). Change the {1,2,3} to {1,2,3,4,5} and you would average the top or bottom five instead.

Example 7: Conditional Average With FILTER

For the last example, let’s look at a modern one that shows how AVERAGE composes with dynamic arrays.

Below is a dataset with orders. Column A has the order ID, column B has the region, and column C has the order amount. Rows for different regions are mixed together in no particular order.

I want the average order amount for the West region only.

Here is the formula:

=AVERAGE(FILTER(C2:C8,B2:B8="West"))

This returns 290. The FILTER function returns just the West amounts (250, 320, 290, and 300) as an array, and AVERAGE reduces that array to a single mean.

This is the composition to remember. AVERAGE never spills on its own, but it happily sits on top of a spilling function.

Important: FILTER is only available in Excel 2021, Excel 2024, and Microsoft 365. In older versions this formula returns a #NAME? error, so use the AVERAGEIF formula below.

For a straightforward single-condition average like this, the AVERAGEIF function is usually the cleaner tool and works in every version of Excel:

=AVERAGEIF(B2:B8,"West",C2:C8)

This also returns 290. Reach for AVERAGEIF when you have one condition, the AVERAGEIFS function when you have several, and the FILTER approach when you want to layer more logic into the array first.

AVERAGE vs MEDIAN vs MODE in Excel

The average isn’t always the best way to describe a typical value. One unusually big or small number can pull it a long way.

Below is a dataset with someone’s commute time, in minutes, over ten workdays. On one day a traffic jam made the commute 85 minutes.

I want to know what a normal commute looks like, so let’s compare three measures.

Here is the AVERAGE formula:

=AVERAGE(B2:B11)

This returns 36. That’s higher than nine of the ten days, because the single 85-minute day drags the mean up.

Now here is the MEDIAN formula:

=MEDIAN(B2:B11)

This returns 30. MEDIAN takes the middle of the sorted values, so one extreme day barely moves it.

And here is the MODE.SNGL formula:

=MODE.SNGL(B2:B11)

This also returns 30, because 30 minutes shows up more often than any other value (four times).

Use AVERAGE when your values are fairly even. When a few extreme values could skew the result, like salaries or house prices, MEDIAN usually gives a better picture.

And use MODE.SNGL when you want the value that comes up most often.

That covers the AVERAGE function from a basic mean to the zero-versus-blank gotcha, non-contiguous cells, and mixing ranges with typed numbers.

It also covers skipping errors, averaging the top values, and a conditional average with FILTER.

Once you know that AVERAGE ignores blanks but counts zeros, most of the surprises go away.

Pick the example closest to your data and adapt the range, and you’ll have your mean in seconds.

List of All Excel Functions

Other Excel Articles You May Also Like:

• Calculating Weighted Average in Excel (Using Formulas)
• Calculating Moving Average in Excel [Simple, Weighted, & Exponential]
• LARGE Function in Excel (7 Examples)