Using the Excel AVERAGE Function


The Excel AVERAGE function returns the average, or arithmetic mean, of the user provided arguments. For example, =AVERAGE(A1:A5) would return the average of the numbers in the range A1:A5.


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

Syntax Breakdown

Required. The first number for which you want to find the average. This can also be a cell reference or range.

Optional. Additional numbers, cell references, or ranges for which you want to find the average. The function can support up to 255 arguments.

Usage Notes

AVERAGE returns the average of the supplied values. Arguments can include cell references, numbers, ranges, arrays, and constants. To calculate the average, Excel adds the provided numbers together and divides by the total number of numbers. The function automatically ignores empty cells.

Excel will recognize logical values and text representation of numbers that are typed directly into the function. Ranges and cell references that contain text or logical values will be ignored.

If you need to include logical values and/or text representation of numbers in a reference, use the AVERAGEA function.

If you need to calculate the average, but only for values meeting a specific criteria, use the AVERAGEIFS function.

Zero Cells and Empty Cells
When calculating the AVERAGE, be aware of the difference between empty cells and those cells that contain the zero value (0) because the show a zero in cells that have a zero value box is checked in the Excel Options dialog box. If this option is selected, empty cells will not be counted, but those with zero values are.

To find the show a zero in cells that have a zero value box follow these steps:

Click the File tab > Options > Advanced > look under display options for this worksheet

Excel AVERAGE Empty Cell Example

AVERAGE, MEDIAN, and MODE are used to measure the central tendency in a statistical distribution. The functions perform as follows.

AVERAGE is used to find the arithmetic mean. This is done by adding up all of the numbers in a group and dividing by the total count of numbers. For example, the average of 1, 2, 3, and 4 is found by adding the numbers together (1+2+3+4 = 10) and dividing by 4 (10/4) to produce 2.5.

The MEDIAN is the middle number in a group of numbers. In the group, 50% of the numbers are above the median and 50% are below the median. For example, in the number set 1, 5, 7, 8, 15 the MEDIAN is 7.

The MODE is the number that occurs most frequently in a group of numbers. In the number set 1, 3, 3, 3, 8 the MODE is 3.