Table of Contents
The AVERAGE and AVERAGEA functions are both used to calculate the average value of a set of numbers in Excel. However, the main difference between the two is how they handle non-numeric values in the data set. AVERAGE function only considers numerical values and ignores any text or blank cells, while AVERAGEA function includes all values, regardless of their data type. This means that AVERAGEA function will include non-numerical values in its calculation, potentially skewing the average value. On the other hand, AVERAGE function will only calculate the average of the numerical values, providing a more accurate result. Therefore, it is important to understand the data set and choose the appropriate function accordingly.
Excel: Difference Between AVERAGE and AVERAGEA Functions
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%.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: