What is the AVERAGEA function? Well, it’s similar to AVERAGE, but it offers additional functionality. At the fundamental level, where all the values in the range defined are numeric, it behaves exactly like the AVERAGE function. Things get a little bit more exciting when the specified range contains an assortment of data types. Get your own copy of the 🔗template used in this tutorial to follow along.
Contents
Purpose
The AVERAGEA function returns the average value of a series of numbers, text, and logical statements.
Syntax
=AVERAGEA (value1, value2, …) or =AVERAGEA ([cell/range])
value1– The first number or range to consider while calculating the average value.value2, …– [OPTIONAL] Additional numbers to consider.
Video Explanation
Examples
Example 1 – Average When all Values are Numeric
In the above range, both AVERAGE and AVERAGEA yield the same result because all values are numeric. What if there were a few non-numeric values in the dataset?
Example 2 – Average When Some Values are not Numeric
Now, the two functions provide different outcomes. They produce different results because AVERAGEA considers Boolean and text values as well when calculating the average. The text “TRUE” is assigned a value of 1 while “FALSE” and any other text values are assigned the value 0. As is the case with the AVERAGE function, AVERAGEA does not include blank cells in the computation.
What if we had a range that consisted of only TRUE & FALSE values?
Example 3 – Average When all Values are Boolean
In column H, AVERAGE gives a #DIV/0! error as we’d expect. AVERAGEA, on the other hand, gives 0.4 as the output, which is correct since there is a total of 20 boxes, and 8 of them are checked. Therefore the average should be 8/20 (Remember TRUE values are assigned a numeric value of 1), which yields 0.4.
Example 4 – AVERAGEA with Numbers and Text
Here we have two columns of data. One column has only numbers, and the other column has numbers and text. In the column on the left, the average is as expected. On the right, however, the AVERAGE function only uses the cells with numerical values. Therefore, AVERAGE is computing the average of only 8 and 9. AVERAGEA, on the other hand, counts text values as 0 and thus is arriving at a lower number by averaging 0, 8, 0, and 9.
Notes
- The AVERAGEA function considers numerical values, bollean values, and text in the calculation.
- It ignores blank cells.
- The AVERAGEA function counts cells containing 0s since zero is a real number.
- The function counts TRUE as 1 and FALSE as 0.
- The AVERAGEA function counts text as 0.
- Consider using the SUBTOTAL function if you have have multiple averages in one column.