AVERAGE

Excel Functions

Get the average of a group of numbers

Syntax

EXCEL

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

Arguments

Parameter	Description
`number1`	A number or cell reference that refers to numeric values.
`number2`	[optional] A number or cell reference that refers to numeric values.

Return Value

A number representing the average.

Details

The AVERAGE function calculates the average of numbers provided as arguments. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can include numbers, cell references, ranges, arrays, and constants. Empty cells, and cells that contain text or logical values are ignored. However, zero (0) values are included. You can ignore zero (0) values with the AVERAGEIFS function, as explained below. The AVERAGE function will ignore logical values and numbers entered as text. If you need to include these values in the average, see the AVERAGEA function. If the values given to AVERAGE contain errors, AVERAGE returns an error. You can use the AGGREGATE fu

Examples

Basic usage

A typical way to use the AVERAGE function is to provide a range, as seen below. The formula in F3, copied down, is:

EXCEL

=AVERAGE(C3:E3)

Blank cells

However, note the zero (0) value in C5 is included in the average, since it is a valid numeric value. To exclude zero values, use AVERAGEIF or AVERAGE

EXCEL

=AVERAGEIF(B3:D3,">0") // exclude zero

Mixed arguments

EXCEL

=AVERAGE(A1,A2,4) // returns 3

Average with criteria

EXCEL

=AVERAGEIFS(C5:C14,D5:D14,"red") // red average
=AVERAGEIFS(C5:C14,D5:D14,"blue") // blue average

Average without #DIV/0!

The average function automatically ignores empty cells in a set of data. However, if the range contains no numeric values, AVERAGE will return a #DIV/

EXCEL

=IF(COUNT(range)>0,AVERAGE(range),"") // check count first

Manual average

To calculate the average, AVERAGE sums all numeric values and divides by the count of numeric values. This behavior can be replicated with the SUM and

EXCEL

=SUM(range)/COUNT(range) // manual average calculation

AVERAGE

Syntax

Arguments

Return Value

Details

Examples

See Also