AVERAGEA Function – Google Sheets

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.

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.

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

Thumbnail for video about averaging
YouTube Tutorial About AVERAGE and AVERAGEA
Thumbnail for YouTube video about averaging
YouTube Tutorial About Issues with the AVERAGE Function

Examples

Example 1 – Average When all Values are Numeric

A series of numbers with the mean being calculated with the AVERAGE and AVERAGEA functions
Just Numbers

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

zeros in a dataset producing different results than blanks
Blanks vs. Zeros

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

bolean values in the AVERAGE and AVERAGEA functions with different results
Checkbox Indicating Order Status

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

The two average functions treating text differently
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.

Leave a Comment