Table of Contents
The AVERAGE and AVERAGEA functions in Excel both calculate the average value in a range, but they do so slightly differently:
The AVERAGE function calculates the mean of all numbers in a column.
The AVERAGEA function calculates the mean of all values in a column.
- This function calculates TRUE and FALSE values to be 1 and 0, respectively.
- This function calculates all text values to be equal to 0.
The following example shows how to use each function in practice with the following dataset in Excel that contains information about various basketball players:
Example 1: AVERAGE vs. AVERAGEA Functions with Text Values
We can type the following formulas into cells E2 and F2 to use the AVERAGE and AVERAGEA functions to calculate the mean value in the Points column:
- E2: =AVERAGE(B2:B8)
- F2: =AVERAGEA(B2:B8)
The following screenshot shows how to use each function in practice:
The AVERAGE function uses only the numeric values in the column to calculate the mean:
- AVERAGE: (15+16+14+12+10+29) / 6 = 16
Meanwhile, the AVERAGEA function uses all of the values in the column to calculate the mean, using 0 for any text values:
- AVERAGEA: (15+16+14+12+0+10+29) / 7 = 13.71428571
Example 2: AVERAGE vs. AVERAGEA Functions with Boolean Values
We can type the following formulas into cells E2 and F2 to use the AVERAGE and AVERAGEA functions to calculate the mean value in the All-Star column:
- E2: =AVERAGE(C2:C8)
- F2: =AVERAGEA(C2:C8)
The following screenshot shows how to use each function in practice:
The AVERAGE function returns the #DIV/0! error because it is not capable of calculating the average of boolean (TRUE and FALSE) values.
Meanwhile, the AVERAGEA function counts TRUE values as 1 and FALSE values as 0, and calculates the following average:
- AVERAGEA: (1+0+0+0+1+0+1) / 7 = 0.42857
This tells us that the percentage of values in the All-Star column that are equal to TRUE is 42.857%.