Excel AVERAGE function to find arithmetic mean

AVERAGE function - 6 things to know about

For the most part, using the AVERAGE function in Excel is easy. However, there are a few nuances that you should be aware of.

1. Cells with zero values are included in the average.
2. Empty cells are ignored.
3. Cells containing text strings and logical values TRUE and FALSE are ignored. If you want to include Boolean values and text representations of numbers in the calculation, use the AVERAGEA function.
4. Boolean values typed directly in a formula are counted. For example, the formula AVERAGE(TRUE, FALSE) returns 0.5, which is the average of 1 and 0.
5. If the specified arguments do not contain a single valid numeric value, a #DIV/0! error occurs.
6. Arguments that are error values cause an AVERAGE formula to return an error. To avoid this, please see how to average ignoring errors.

Calculate average percentage in Excel

To get an average of percentages, you use a normal Excel formula for average. The key thing is to set the Percent format for the formula cell.

For example, to calculate an average percentage in cells C2 through C11, the formula is:

=AVERAGE(C2:C11)

Get average time in Excel

Calculating different time units manually, would be a real pain… Luckily, the Excel AVERAGE function copes with times perfectly. For the time average to display correctly, just remember to apply an appropriate time format to the formula cell.

For instance, to find an average time in the below dataset, the formula is:

=AVERAGE(B3:B13)

Excel average without zeros

The Excel AVERAGE function skips blank cells, text and logical values, but not zeros. In the image below, notice that the average in cells E4, E5 and E6 is the same as in E3 as a blank cell and invalid values in column C are ignored and only the numbers in columns B and D are processed. However, the zero value in C7 is included in the average in E7, since it is a valid numeric value.

To exclude zeros, use the AVERAGEIF or AVERAGEIFS function instead. For example:

=AVERAGEIF(B3:D3, "<>0")

Average top or bottom N values

To get an average of top 3, 5, 10 or n values in a range, use AVERAGE in combination with the LARGE function:

For instance, to get an average of the 3 largest numbers in B3:B11, the formula is:

=AVERAGE(LARGE(B3:B11, {1,2,3}))

To calculate a mean of bottom 3, 5, 10 or n values in a range, use AVERAGE together with the SMALL function:

For instance, to average the 3 lowest numbers in the range, the formula goes as follows:

=AVERAGE(SMALL (B3:B11, {1,2,3}))

And here are the results:

How this formula works:

Normally, the LARGE function determines the Nth largest value in a given array. To get the top n values, an array constant such as {1,2,3} is used for the second argument.

In our case, LARGE returns the highest 3 values in the range, which are 94, 93 and 90. AVERAGE takes it from there and outputs the mean.

The AVERAGE SMALL combination works in a similar way.

AVERAGE IF with one condition

To average numbers that meet a certain condition, use this generic formula:

In Excel 2019 and lower, this only works as an array formula, meaning you need to press the Ctrl + Shift + Enter keys to complete it correctly. In Excel 365 and 2021, a normal formula will work nicely.

As an example, let's find an average Math score in the table below. For this, just use "Math" for criteria:

=AVERAGE(IF(C3:C11="Math", B3:B11))

Or you can input the condition in some cell and reference that cell (F2 in our case):

=AVERAGE(IF(C3:C11=F2, B3:B11))))

How this formula works

The logical test of the IF function compares each subject in C3:C11 against the target one in F2. The result of the comparison is an array of TRUE and FALSE values, where TRUEs represent matches:

{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

For the value_ if_true argument, we supply the range of scores (B3:B11), so if the logical test is TRUE, the corresponding score is returned. As the value_ if_false argument is omitted, FALSE appears where the condition is not met:

{FALSE;FALSE;FALSE;74;67;FALSE;FALSE;59;FALSE}

This array is fed to the AVERAGE function, which calculates an arithmetic mean of the numbers ignoring the FALSE values.

AVERAGE IF with multiple criteria

To average numbers with several criteria, the generic formula is:

For example, to average the Math scores in class A, you can use this formula:

=AVERAGE(IF((C3:C11="Math") * (D3:D11="A"), B3:B11))

With cell references for criteria, this works equally well:

=AVERAGE(IF((C3:C11=G2) * (D3:D11=G3), B3:B11))

In Excel 2019 and lower, both of the above should be array formulas, so remember to press Ctrl + Shift + Enter to get an accurate result. In Excel 365 and 2021, a normal Enter key will work fine due to inbuilt support for dynamic arrays.

The same result can be achieved with the help of the nested IF statement:

=AVERAGE(IF(C3:C11=G2, IF(D3:D11=G3, B3:B11)))

Which formula to use is just a matter of your personal preference.

How this formula works

In the logical test of IF, two comparison operations are performed - first, you check the list of subjects in C3:C11 against the value in G2, and then you compare the classes in D3:D11 against the value in G3. The two arrays of TRUE and FALSE values are multiplied, and the multiplication operation works like the AND operator. In any arithmetic operation, TRUE equates to 1 and FALSE equates to 0. Multiplying by 0 always gives zero, so the resulting array has 1 only when both conditions are TRUE. This array is evaluated in the logical test of the IF function, which returns the scores corresponding to 1's (TRUE):

{FALSE;FALSE;FALSE;74;67;FALSE;FALSE;FALSE;FALSE}

This final array is served to AVERAGE.

AVERAGE and IFERROR

Before averaging, filter out errors with the help of the IFERROR function:

In all versions except Excel 365 and 2021 where arrays are handled natively, hit the Ctrl + Shift + Enter keys together to make it an array formula.

For example, to average the below range of cells without errors, the formula is:

=AVERAGE(IFERROR(B3:B13, ""))

AGGREGATE function

Another easy way to average ignoring errors is using the AGGREGATE function. To configure AGGREGATE for this purpose, you set the function_num argument to 1 (AVERAGE function), and the options argument to 6 (ignore error values).

For instance:

=AGGREGATE(1, 6, B3:B13)

As you can see in the screenshot, both functions work beautifully: