Purpose
Return value
Syntax
=AVERAGEA(value1,[value2],...)- value1 - A value or reference to a value that can be evaluated as a number.
- value2 - [optional] A value or reference to a value that can be evaluated as a number.
How to use
The AVERAGEA function returns the average of a set of supplied values. AVERAGEA will include the logical values TRUE and FALSE, and numbers represented as text in the calculation. The AVERAGE function ignores these values during calculation
AVERAGEA takes multiple arguments in the form of value1, value2, value3, etc. up to 255 total. Arguments can include numbers, cell references, ranges, arrays, and constants. Empty cells are ignored, but zero (0) values are included.
Examples
To average values in the range A1:A10, including logical the logical values TRUE (1) and FALSE (0) and numbers entered as text, use AVERAGEA like this:
=AVERAGEA(A1:A10) // average numbers, logicals, numbers as text
One confusing aspect of the AVERAGE function compared to the AVERAGEA function is that both functions will evaluate logicals and numbers entered as text when they are hardcoded as constants in a formula:
=AVERAGE(TRUE,2) // returns 1.5
=AVERAGEA(TRUE,2) // returns 1.5
=AVERAGE("3",2) // returns 2.5
=AVERAGEA("3",2) // returns 2.5
However, the AVERAGE function will ignore logicals or numbers entered as text when they appear in cell references. You can see this behavior in the worksheet example shown above.
Notes
- Values can be supplied as numbers, ranges, named ranges, or cell references that contain values. Up to 255 arguments can be supplied.
- To calculate the average, Excel adds the numeric value of each value together and divides by the total number of values supplied.
- AVERAGEA evaluates TRUE as 1 and FALSE as zero.
Related functions
AVERAGE Function
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
AVERAGEIF Function
The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria. AVERAGEIF criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
AVERAGEIFS Function
The Excel AVERAGEIFS function returns the average of cells that meet multiple conditions, referred to as criteria. To define criteria, AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
