Let me start this topic with a question. Do you know when one should use the AVERAGEA function in Google Sheets?

The question won’t arise if you know the purpose of the AVERAGEA function in Google Sheets.

The purpose of the said function is to calculate the average (arithmetic mean) of values (numbers, texts, and Boolean TRUE, FALSE) in a range or list of arguments.

There are other functions for calculating the average (arithmetic mean).

They are AVERAGE, DAVERAGE, AVERAGEIF, and AVERAGEIFS.

We will discuss how the AVERAGEA function stands out from the rest after the syntax and arguments part.

AVERAGEA Function Syntax, Arguments, and Examples in Google Sheets

Syntax of the AVERAGEA Function: AVERAGEA(value1, [value2, ...])

Arguments:

value1 – The first value or range for calculating the arithmetic mean.

Example of first value:

=averagea(100)

Example of first range:

=averagea(A1:A10)

value2 – Additional value or range for calculating the arithmetic mean.

Example of additional value:

=averagea(100,150)

Example of additional range:

=averagea(A1:A10,C1:C10)

Google Sheets supports an arbitrary number of arguments in an AVERAGEA formula though the specified number is 30.

Also, please note that value1 is required and subsequent values (value2, value3, etc.) are optional.

Difference Between the AVERAGEA and AVERAGE Functions in Google Sheets

Please check the below table to understand the difference between the AVERAGE and AVERAGEA functions in Google Sheets.

Here is an example where we can exclusively use the AVERAGEA function in Google Sheets.

If you use other functions in this scenario, they may return the #DIV/0 error.

I have the marks of 20 students in an exam and want to find the average of students who scored greater than or equal to 80.

To check whether the students have scored marks above or equal to 80, I have used the below array formula in cell E2.

=ArrayFormula(D2:D21>=80)

Note:- Since the formula covers rows 2 to 21, we should empty E2:E21 before inserting it in cell E2.

The above formula returns TRUE wherever the marks are >= 80 and FALSE for <80.

To find the average of students who have scored marks >=80, we can use the below AVERAGEA formula in Google Sheets.

=averagea(E2:E21)

The formula will convert TRUE to 1 and FALSE to 0 and then calculate the mean.

As a side note, we can use the E2 formula as the value1 argument in the above formula and thus avoid using column E. Here it is.

=ArrayFormula(averagea(D2:D21>=80))

We can use the AVERAGE instead of the AVERAGEA function in the above example after converting the Boolean values to numbers.

Just insert two hyphens in the logical test part of the formula, and voila!

=ArrayFormula(average(--(D2:D21>=80)))

DAVERAGE, AVERAGEIF, and AVERAGEIFS – Conditional Arithmetic Mean

Usually, we compare the AVERAGE and AVERAGEA functions, and I have already done the same above.

Because we can specify conditions in all the other functions.

I mean to say, the functions DAVERAGE, AVERAGEIF, and AVERAGEIFS can filter the range and then calculate the arithmetic mean.

If we ignore conditions, these three functions are close or similar to the AVERAGE as all of them also ignore TRUE, FALSE, and Text values in the range.

Regarding AVERAGE and AVERAGEA functions, we require the help of the FILTER or QUERY to apply conditions.

Here is an example of using conditions in all the above five functions. For sample data, please refer to the above screenshot.

Let’s find the average marks of students in Division B.

AVERAGEIF Formula:

=averageif(C2:C21,"B",D2:D21)

AVERAGEIFS Formula:

=averageifs(D2:D21,C2:C21,"B")

DAVERAGE Formula:

=daverage(A1:E21,4,{"Division";"B"})

You May Like:- The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.

AVERAGE Formula:

=average(filter(D2:D21,C2:C21="B"))

AVERAGEA Formula:

=averagea(filter(D2:D21,C2:C21="B"))

That’s all about the AVERAGEA function in Google Sheets. Thanks for the stay. Enjoy!

Resources

1. GEOMEAN for Geometric Mean Calculation in Google Sheets.
2. AVERAGE.WEIGHTED: Calculate the Weighted Average in Google Sheets.
3. How to Use the HARMEAN Function in Google Sheets.
4. How to Use the TRIMMEAN Function in Google Sheets.