Exceljet

Quick, clean, and to the point

Excel AVERAGE Function

Excel AVERAGE function
Summary 

The Excel AVERAGE function returns the average of values provided. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Purpose 
Get the average of a group of numbers
Return value 
A number representing the average.
Syntax 
=AVERAGE (number1, [number2], ...)
Arguments 
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.
Usage notes 

The AVERAGE function calculates the average of numbers provided as arguments. To calculate the average, Excel adds the numbers together and divides by the total number of values supplied.  AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Manual calculation

Excel calculates an average (arithmetic mean) by adding numeric values and dividing by the count of numeric values. This behavior can be duplicated using the SUM and COUNT functions like this:

=SUM(A1:A5)/COUNT(A1:A5) // manual average calculation

Examples

The example below shows how values can be hardcoded directly into the AVERAGE function:

AVERAGE function with constants

=AVERAGE (2,3,4) // returns 3

AVERAGE can also be used to calculate the average of a range of cells:

AVERAGE function with range

=AVERAGE(A1:A3) // returns 3

The AVERAGE function can handle up to 255 arguments, which can be a mixture of references and constants:

AVERAGE function with cell references and constants

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

See more examples of the AVERAGE function below.

Other options

The AVERAGE function will include zero values when calculating an average. To exclude zero values, or other values based on logical criteria, see the AVERAGEIF or AVERAGEIFS.

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 provided to AVERAGE function contain errors, AVERAGE returns an error. You can use the AGGREGATE function to ignore errors.

Notes

  1. AVERAGE automatically ignores empty cells and cells with text values.
  2. AVERAGE includes zero values. Use AVERAGEIF or AVERAGEIFS to ignore zero values.
  3. Arguments can be supplied as constants, ranges, named ranges, or cell references.
  4. AVERAGE can handle up to 255 total arguments.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.