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 0
s 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.