The Excel AVERAGE Function


Basic Description

The Excel AVERAGE function returns the arithmetic mean of a list of supplied numbers.

The syntax of the function is :

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

where the number arguments are a set of one or more numeric values, or arrays of numeric values, for which you want to calculate the average - these values can be supplied directly to the function, or as one or more cells or ranges of cells containing numeric values.

In Excel 2007 & Excel 2010, you can provide up to 255 number arguments to the Average function, but in Excel 2003, the function can only accept up to 30 arguments. However, each argument can consist of an array of values or a range of cells, each of which can contain many values.


Excel Average vs. Excel Averagea

The Excel Average Function is very similar to the Excel Averagea Function. The only difference between these two functions is the way in which logical values, or text values within arrays or references are treated in the calculation of the arithmetic mean. This is shown in the table below:

AVERAGE Function AVERAGEA Function
Logical values or text representations of numbers, typed directly into the list of arguments ARE counted
(TRUE=1, FALSE=0)
ARE counted
(TRUE=1, FALSE=0)
Text that cannot be interpreted as a number, typed directly into the list of arguments #VALUE! error #VALUE! error
Logical values, within arrays or reference arguments Ignored ARE counted
(TRUE=1, FALSE=0)
Text (including empty text "", text representations of numbers, or other text), within arrays or reference arguments Ignored Counted as zero
Empty cells Ignored Ignored


Excel Average Function Examples

The following spreadsheet shows the Excel Average function used to calculate the arithmetic mean of the set of values in cells A1-A5. Although the same 5 values are provided to each of the functions in cells B1-B4, in each case, the values are provided to the function in different ways.

 Formulas:
  A B
1 8 =AVERAGE( A1:A5 )
2 7 =AVERAGE( 8, 7, 9, 6, 10 )
3 9 =AVERAGE( A1, A2, A3, A4, A5 )
4 6 =AVERAGE( A1:A3, {6, 10} )
5 10  
 Results:
  A B
1 8 8
2 7 8
3 9 8
4 6 8
5 10  

Further examples of the Excel Average function can be found on the Microsoft Office website.


Average Function Error

The most common error from the Excel Average function is the #DIV/0! error :

Common Error
#DIV/0! - Occurs if the values to be averaged are all non-numeric
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net