Excel Average Functions
Examples show how to average Excel cells with numbers, text, blanks,
or cells based on specific criteria. Use the AVERAGE, AVERAGEA, AVERAGEIF
or AVERAGEIFS functions, or TRIMMEAN, for an average that excludes outliers.
Average Cells with Numbers -- AVERAGE
The Excel AVERAGE function will calculate the average (arithmetic mean) for a specified set of numbers in an Excel spreadsheet.
It is one of the central tendency functions, like MEDIAN and MODE.
AVERAGE Syntax
The AVERAGE function syntax has the following arguments:
- =AVERAGE(number1, [number2], ...)
The arguments (e.g. number1) can be cell references, or cell ranges, or numbers typed
into the AVERAGE formula.
In the list of arguments, you can include up to 255 arguments, with numbers to include in the average

AVERAGE Function Example
The following AVERAGE function example uses one argument -- a reference
to cells A1:A5.
- Enter the sample data on your worksheet
- In cell A7, enter an AVERAGE formula, to average the numbers in
column A: =AVERAGE(A1:A5)

- Press the Enter key, to complete the formula.
- The result will be 21.83, the average of the cells that contain
numbers.
Cell A1 isn't included in the average, because it contains text,
and the empty cell C3, is ignored.
Note: Since dates are stored as numbers, the AVERAGE function
will include any cells that contain dates.
Average Cells with Data -- AVERAGEA
The AVERAGEA function will average cells that are not empty. Its
syntax is:
=AVERAGEA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed
into the formula. The following example uses one argument -- a reference
to cells A1:A5.
- Enter the sample data on your worksheet
- In cell A7, enter a AVERAGEA formula, to average the numbers in
column A: =AVERAGEA(A1:A5)

- Press the Enter key, to complete the formula.
- The result will be 16.38, the average of the cells that contain
data. Cell A1 contains text, which is treated as a zero, and cell
A3 is not included in the average, because it is blank.
Note: AVERAGEA will include cells with formulas including
those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2).
Average cells that match criteria -- AVERAGEIF
This short video show how to create a flexible AVERAGEIF formula, to calculate the average quantity sold, for the selected product name. There are written steps below the video, and the sample file is in the Download section
Match criterion exactly
In Excel, calculate average of the numbers for cells that meet a specific criterion. In this example
only the quantities for the Pen orders will be averaged.
- Select the cell in which you want to see the average (cell A12
in this example)
- Type an equal sign (=) to start the formula
- Type: AVERAGEIF(
- Select the cells that contain the values to check for the criterion.
In this example, cells A1:A10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for text,
so type the word in double quotes: "Pen"
Note: upper and lower case are treated equally
- Type a comma, to separate the arguments
- Select the cells that contain the values to average. In this example,
cells B1:B10 contain the values
- Type a closing bracket
The completed formula is: =AVERAGEIF(A1:A10,"Pen",B1:B10)

- Press the Enter key to complete the entry
- The result will be 9.25, the average of the values for rows that contain
"Pen"

Match criterion in a string
In Excel, average cells in rows that contain a criterion as part
of the cell's contents. In this example all Pen, Gel Pen, and Pencil
orders will be averaged, because they contain the string "pen".
- Select the cell in which you want to see the calculated average (cell A12
in this example)
- Type an equal sign (=) to start the formula
- Type: AVERAGEIF(
- Select the cells that contain the values to check for the criterion.
In this example, cells A1:A10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for text,
so type the word in double quotes, with one or more asterisk (*)
wildcard characters: "*Pen*"
Note: upper and lower case are treated equally
- Type a comma, to separate the arguments
- Select the cells that contain the values to average. In this example,
cells B1:B10 contain the values
- Type a closing bracket
The completed formula is: =AVERAGEIF(A1:A10,"*Pen*",B1:B10)

- Press the Enter key to complete the entry
- The result will be 8.83, the average of a range of rows that contain the
string, "Pen", in column A

Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 7 above could be
changed to:
=AVERAGEIF(A1:A10,"*" & B12 &
"*",B1:B10)
if cell B12 contained the text pen.
Criterion and operator
You can use an operator with a criterion. In this example only the
rows where the quantity is greater than or equal to ten will be averaged.
- Select the cell in which you want to see the average (cell A12
in this example)
- Type an equal sign (=) to start the formula
- Type: AVERAGEIF(
- Select the cells that contain the values to check for the criterion.
In this example, cells B1:B10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for rows
where the quantity is greater than or equal to 10. The >= operator
is used before the number, and the entire criterion is enclosed
in double quotes: ">=10"
Note: Even though this is a numerical criterion, it must enclosed
in double quote marks.
- Type a closing bracket
- The completed formula is:
=AVERAGEIF(B1:B10,">=10")
- Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 8 above could be
changed to:
=AVERAGEIF(B1:B10,">=" & B12)
if cell B12 contained the number 10.
Average cells for multiple criteria
-- AVERAGEIFS
Match multiple criteria
In Excel 2007 and later versions, you can use the AVERAGEIFS function
to average rows that meet two or more criteria. In this example only
the rows where the item is "Pen" and the quantity is greater
than or equal to ten will be averaged.
The criteria have been entered in cell D3 and E3. We'll refer to
those cells in the formula, instead of typing the values in the formula.
- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: AVERAGEIFS(
- Select the cells that contain the values to average. In this example,
cells B2:B10 will be averaged
- To start the next argument, type a comma
- Select the cells that contain the values to check for the first
criterion. In this example, cells A2:A10 will be checked
- Type a comma, and click on cell D3, which contains the first criterion,
"Pen"
- To start the next set of criteria, type a comma
- Select the cells that contain the values to check for the second
criterion. In this example, cells B2:B10 will be checked
- Type a comma, and the operator for the second criterion: ">="
Note: The operator is enclosed in double quote marks.
- Type an ampersand (&), and click on cell E3, which contains
the minimum number for the averaged cells -- 10.
- To start the next argument, type a comma
- Select the cells that contain the values to check for the second
criterion. In this example, cells B2:B10 will be checked
- Finish with a closing bracket: )
- The completed formula is shown below.
- Press the Enter key to complete the entry
The completed formula is:
=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">=" & E3)

Note: Instead of refering to cells for the criteria, you
can type them into the formula. Here is the same formula, with typed
criteria:
=AVERAGEIFS(B2:B10,A2:A10,"Pen",B2:B10,">=10")
Average Rows in a Filtered List -- SUBTOTAL
After you filter the rows in a list, you can use the SUBTOTAL function
to average the visible rows.
- Apply an AutoFilter to the table. There are instructions here
-- AutoFilter Basics
- Filter at least one of the columns in the table. In this example,
the first column has been filtered for Binders.
- Select the cell immediately below the column you want to sum.
- Click the AutoSum button on the Ribbon's Home tab or Formulas
tab.
- If you want the SUBTOTAL function in a cell other than the
one directly below the filtered list, you can type the formula,
instead of using the AutoSum button.
- A SUBTOTAL formula will be automatically inserted, totalling the
visible cells in the column
- The first argument in the SUBTOTAL function is a function number,
that specifies how the numbers should be calculated. The default
is 9, which tells Excel to SUM the numbers.
- Other function numbers can be used, such as 1 for AVERAGE, or
2 for COUNT.
- To Average all the non-empty cells in column D, select 1 - AVERAGE
from the drop down list of functions, as the first argument:
=SUBTOTAL(1,D2:D10)

- Check the range of cells to be averaged, and correct the range
address, if necessary.
- Press the Enter key to complete the formula entry.
Note: In the Subtotal function, you can use 101, instead
of 1:
=SUBTOTAL(101,D2:D10)
to subtotal rows which have been manually hidden, as well as filtered
rows. 
Video: Trimmed Mean With TRIMMEAN Function
To exclude a specific percentage of outlying data from an average,
you can use Excel's TRIMMEAN function. Watch this video to see how
to set up the formula, and the written instructions are below the
video.
Trimmed Mean With TRIMMEAN Function
To exclude outlying data from an average, you can use the TRIMMEAN
function. This function excludes a specific percentage of data points
from the top and bottom of the data set, then returns the average
(mean) of the remaining data points.
NOTE: In this example, the quantities are sorted in ascending
order, so it is easier to see the top and bottom numbers. Numbers
do NOT need to be sorted, for the TRIMMEAN function to calculate correctly.
TRIMMEAN Function
The TRIMMEAN function requires 2 arguments: TRIMMEAN(array, percent)
In this example,
- the values for the array are in cells B2:B21
- the trim percent is entered in cell E3, as 25% (or 0.25)
To calculate the trimmed mean, enter this formula in cell E4::
=TRIMMEAN(B2:B21, E3)
The TRIMMEAN result (53.06) is different from the AVERAGE (51.95),
which is shown in cell E5.

How TRIMMEAN Works
In this example, there are 20 values, and the trim percent is 25%.
To calculate how many number to trim,
- the values are counted, then multiplied by the trim percentage
(e.g. 20 * .25 = 5
- That number is divided by 2, to get the number to trim at each
end ( e.g. 5 / 2 = 2.5)
- To remove an equal number of data points at each end, the number
is rounded down to the nearest integer ( e.g. INT( 2.5) = 2)
So, in this excample, the top 2 (86,97) and bottom 2 (3,4) data points
will not be included in the average that TRIMMEAN calculates.
The AVERAGE function, used on cells B4:B19 in this example, returns
the same result as the TRIMMEAN function, with a trim percentage of
25%.

Get the Workbook
To see the formulas and test data used in this example, you can download
the Average functions
sample file. The file is in xlsx format and does not contain any macros
Excel Function Tutorials
Sum Functions
MEDIAN Function
VLOOKUP Function
INDEX / MATCH Functions
Count Functions
INDIRECT Function