AVERAGEA Function

The Excel AVERAGEA function is part of the Statistical function and is supported in all Excel versions. It is used to calculate the arithmetic mean of the mixed set of data points. This function includes the Boolean value: TRUE is treated as 1, FALSE is treated as 0, and the letters are considered as 0. For example, it can be used to track employee attendance, calculate the average sales production, compute the arithmetic mean of the profit margin, evaluate the average risk measures, and more.

Compatibility

This advanced Excel function is compatible with the following versions of MS Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2024
Excel 2024 for Mac
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2016
Excel 2013
Excel 2010
Excel 2007

Syntax

The syntax of the AVERAGEA function is as follows −

=AVERAGEA(value1, [value2] ...)

Arguments

You can use the following arguments with the AVERAGEA function −

Argument	Description	Required / Optional
Value1	It specifies a cell ranges, arrays, text representation of numbers, and Boolean values(TRUE/FALSE, arrays, and names). It may take up to 255 cell values upon which you may apply the AVERAGEA function.	Required
Value2,...		Optional

Points to Remember

The AVERAGEA function includes the hidden cells, and date serial number.
Boolean values(TRUE/FALSE), zero values are countable if directly defined in the argument list of this function.
Logical values and text representations of numbers that you type directly into the list of arguments are counted.
Utilize AVERAGE function to exclude the logical and number’s text depiction in a reference during calculation.
If all the arguments contain the cell references that point to blank cells, then the AVERAGEA function will retrieve #DIV/0! Error.
If the text values are directly specified to the any of the arguments, then the AVERAGA function will retrieve the #VALUE! error.

Examples of AVERAGEA Function

Practice the following examples to learn the use of the AVERAGEA function in Excel.

Example 1: What does the AVERAGEA Function do in Excel?

In this example, we will understand the fundamental concept of the executing the AVERAGA function for the mixed type of field values.

Solution

Step 1 − Consider the sample dataset where the different types of field values are defined in the cell range B2:B7. You may enter the formula =AVERAGEA(B2:B7) in the C2 cell. Here, the text values and 0 values are included in the AVERAGA calculations. However, the blank cell B5 is not counted.

Step 2 − Once you hit the Enter tab, the AVERAGEA function will return the value of 17.6.

Step 3 − Furthermore, you may write the formula =AVERAGEA("jenny","hary","john") in the C3 cell. In this expression, all arguments contain the text values, which is strictly prohibited.

Hence, the AVERAGEA function returns the #VALUE! error.

Example 2

If all the arguments contain the cell references that point to blank cells, then the AVERAGEA function will retrieve #DIV/0! Error.

Solution

You may enter the formula =AVERAGEA(C2:C5) in the B2 cell. Here, all the cell references are empty.

Therefore, the AVERAGA function returns the #DIV/0! Error.

Download Practice Sheet

You can download and use the sample data sheet to practice the AVERAGEA function.

Save or share this article