Using the Excel AVERAGEA Function
SummaryThe Excel AVERAGEA function returns the average, or arithmetic mean, of the user provided values in a list of arguments.
AVERAGEA, unlike the AVERAGE function, will evaluate the logical values TRUE and FALSE and numbers represented as text.
Syntax=AVERAGEA (value1, [value2], ...)
Required. The first value for which you want to find the average.
Optional. Additional values, cell references, or ranges for which you want to find the average. The function can support up to 255 arguments.
Usage NotesAVERAGEA returns the average of a group of supplied values. Arguments can include cell references, numbers, ranges, and named ranges. To calculate the average, Excel adds the values together and divides by the total number of values provided.
AVERAGEA will evaluate the logical value TRUE as 1 and FALSE as zero (0). Empty text ("") will evaluate as zero (0).
To calculate the average, but only for values meeting a specific criteria, use the AVERAGEIFS function.
Use the AVERAGE function if you do not want to include logical values and numbers provided as text in a reference as part of the calculation.
Zero Cells and Empty Cells
When using AVERAGEA, 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
AVERAGE vs MEDIAN vs MODE
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.