The Excel AVERAGEA Function

Related Functions:
AVERAGE Function

Basic Description

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

The syntax of the function is:

AVERAGEA( value1, [value2], ... )

where the value arguments are a set of one or more numeric values, or arrays of numeric values for which you want to calculate the average.

In current versions of Excel (Excel 2007 and later), you can provide up to 255 value arguments to the Averagea function, but in Excel 2003, the function can only accept up to 30 arguments. However, each argument can be an array of values or a range of cells, each of which can contain many values.


Excel Average vs. Excel Averagea

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

AVERAGE FunctionAVERAGEA Function
Logical values or text representations of numbers, typed directly into the list of argumentsARE 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 argumentsIgnoredARE counted
(TRUE=1, FALSE=0)
Text (including empty text "", text representations of numbers, or other text), within arrays or reference argumentsIgnoredCounted as zero
Empty cellsIgnoredIgnored


Excel Averagea Function Examples

The following spreadsheet shows four examples of the Excel Averagea function used to calculate the arithmetic mean of the values 8, 1, 9, 6 & 10.

Although the same 5 values are provided to each of the functions in cells B1-B4, these values are provided to the function in different ways.

 Formulas:
 AB
18=AVERAGEA( 8, 1, 9, 6, 10 )
21=AVERAGEA( {8,1,9}, 6, 10 )
39=AVERAGEA( A1, A2, A3, A4, A5 )
46=AVERAGEA( A1:A5 )
510 
 Results:
 AB
186.8
216.8
396.8
466.8
510 

The examples in the above spreadsheets show that each argument to the Averagea function can be supplied as a single number or cell, or as an array of numbers or cells (note that in cell B2, the argument {8,1,9} is an array of numbers).


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


Averagea Function Error

If you get an error from the Excel Averagea function, this is likely to be one of the following:

Common Error
#DIV/0!-Occurs if the arguments to the Averagea function are all references to blank cells.
#VALUE!-Occurs if any of the arguments that are supplied directly to the Averagea function are text values that can not be interpreted as numbers.