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.

## 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.