The AVERAGEA calculated the mean of a set of values similar to AVERAGE, but offers additional functionality. When all inputs to the function are numbers, it produces the same result as the AVERAGE function. However, when the inputs are text or boolean values, the AVERAGEA function acts differently than AVERAGE. Get your 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, …)
value1– The first number or range to consider while calculating the average value.value2, …– [OPTIONAL] Additional numbers to consider.
Insert Math Symbols (Add-On)
Similar Functions
AVERAGE – Calculates the mean value of a set of numbers. Ignores text values.
MEDIAN – Finds the middle number in a list of values.
SUBTOTAL – Find the average while ignoring other averages.
Video Tutorials
Examples
Example 1 – Average When all Values are Numeric
In the image above, the AVERAGEA function computes the average values of the customers’ items ordered in cells C2 through C7. The calculation adds the values and divides them by six.
The AVERAGE and AVERAGEA functions return the same result because the range contains only numbers. What if there were a few non-numeric values in the range?
Example 2 – Average When Some Values are Blank
As we move into the following example, you will see that the two functions still produce the same result.
The two Items Ordered columns above have the same total number of items. However, the 0s in rows 3 and 5 of column C are actual zeros. In contrast, rows 3 and 5 are blank in column D. The AVERAGE and AVERAGEA functions only consider values that are not blank and are numerical. Therefore, these functions treat 0 as a number in the numerator and denominator, while the blanks are left out. Consequently, the calculations on the right do not use cells D3 and D6. These two cells are null values, meaning they’re blank. Ignoring these two cells increases the result of the functions from 2 to 4.
What if we had a range that consisted of only TRUE & FALSE values?
Example 3 – Average When all Values are Boolean
Now, the outcomes of the two functions diverge as we look at checkboxes. As you can see, all values in the range C2:C7 are checkboxes. Google Sheets treats checkboxes as boolean values. A checked box is TRUE, and an unchecked box is FALSE.
The formula in cell C9: =AVERAGE(C2:C7)
The formula in cell C10: =AVERAGEA(C2:C7)
Since the AVERAGE returns a #DIV/0 error (cell C9) for a range with only boolean values, you must use the AVERAGEA function instead. AVERAGEA treats a checked box’s value of TRUE as 1 and an unchecked box’s value of FALSE as 0. In the example, AVERAGEA returns 0.83, which means that 83% of the boxes are checked.
Example 4 – AVERAGEA with Numbers and Text
For the last example, we have a column of data with numbers and text. Imagine the spreadsheet user intended to enter the value of $5.48 into cell C3 but ended up with the text "five forty-eight" instead. These two values sound the same when spoken but are quite different as entered.
The AVERAGE function only includes the cells with numerical values, but AVERAGEA includes the text values as well. However, since the text "five forty-eight" is not a number, AVERAGEA will count it as a 0. Therefore, AVERAGE is computing $1,159.42 divided by 5, equaling $231.88, while AVERAGEA is computing $1,159.42 divided by 6 for a total of $193.24.
The different treatment causes the two results to be significantly different. Be sure to choose which function makes the most sense for your data.
Live Examples in Sheets
Go to this spreadsheet for examples of the AVERAGE and AVERAGEA functions shown above that you can study and use anywhere you would like.
Notes
- The AVERAGEA function considers numbers, boolean values, and text in the calculation.
- It ignores blank cells.
- 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 multiple averages in one column.