AVERAGEA Function

What Is The AVERAGEA Function In Excel?

The AVERAGEA Function is an Excel Statistical Function that is utilized to sum the average of a given set of data, including texts and logical values.

It is a variation Average function that finds the Average of a range of values; it is an element of the Statistical function in Excel. The AVERAGEA function is pretty much similar to the AVERAGE function, but there are a few differences like:

1. The AVERAGE function ignores the texts, whilst the AVERAGEA function treats them as "0".
2. In the case of Logical Values, the AVERAGE function ignores them, and AVERAGEA function treats TRUE and FALSE as "1" and "0".
3. The syntaxes are different from each other, as the AVERAGEA function's syntax is Averagea(value1, value2, value,...), AVERAGE function's syntax is =AVERAGE(A1:A3)
4. The AVERAGEA function considers the texts and logical values into consideration. Whilst, AVERAGE function totally ignore them.
5. You can use the AVERAGEA function when there is a need to include numbers, text, and logical values in the arguments

The syntax used for the function is:

Averagea(value1, value2, value3,...)

In simple terms, the function's primary role is to consider the numeric and textual values in the sheet.

These functions are applicable in most versions of Microsoft Excel, such as Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 (only for MAC), Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.

An interesting point to note is that many confuse the “Average function” and the “Averagea function.” The difference lies in the way the function calculates the numeric values.

In the ‘Averagea Function,’ specific values are assigned to either the boolean values, i.e., ‘true & false,’ or other text forms while performing the calculation. The TRUE value is treated as 1, while the FALSE value is treated as 0.

If the cell contains a non-numeric (i.e., text) or empty text, the value is also considered 0. There is always a risk that the data sets are sometimes considered dates or times.

If the “above-mentioned criteria are not met,” then the Excel sheet returns an empty cell, i.e., 0.

If the sheet does not contain rows or columns to compile, then the cell remains blank.

Whereas in the ‘Average Function,’ these values are equal to that of NIL – which means that excel overlooks the text/boolean values and performs the function.

The formula of the function: Assuming that there are five values entered in the sheet, then the procedure is applied as follows:

Averagea (Value 1: Value 5)

Examples of AVERAGEA & Average function

The following examples portray how the functions operate differently. Regarding the data entered in each cell, the former inculcates the cell in its calculation, while the latter overlooks the textual and non-numeric data.

Example #1

Let us consider that there are values entered in from Cell 3A to Cell 3E as follows – 5, 4, 6, TRUE, 0. The Averagea formula, when applied, will give the following result:

I.e., ((5 + 4 + 6 + TRUE + 0)/ 5 ) = 3.2. Cell 3D has been assigned the value, which is why the range used to calculate is 5 rather than 4.

However, for a better understanding, lets us see an example of the ‘Average Function:

Example #2

Take the same example above with the same values from Cell 3A to Cell 3E (5, 4, 6, TRUE, 0). The formula shall be applied as follows:

In the above picture, it can be observed that the total values from Cell 3A to Cell 3D are calculated using four as the entire range. The Excel formula overlooks the value assigned in Cell D. Therefore, the formula applied would be—((5 + 4 + 6 + 0)/ 4) = 3.75.

From the above examples, the main difference between the two is the values taken into account in terms of boolean values (True & False), textual and non-numeric data. Due to this, the total Sum (i.e., the divisor) is different for both functions.

AVERAGEA Vs. AVERAGE Function: When To Use?

If the calculation does not require the inclusion of arguments that are non-numeric or boolean values, then it is suitable to use the AVERAGE function.

However, if the data includes non-numeric arguments and boolean values, it is suitable to use the AVERAGEA function.

Averagea Function Vs. Average Function (Differences and Similarities):

Henceforth, summarizing the table, it can be concluded that if the user intends to overlook cells that contain a textual, non-numeric, or boolean value, then it is best advised to use the Average function where the cells containing boolean values, textual and non-numeric data are not included in the divisor.

Moreover, in reference to point No. 5, rather than using the syntax and typing down the command in the cell, the order can opt from the “Home tab” of Excel in the AutoSum (∑).

An in-depth overview of the AVERAGEA function

So far, we have seen how and in what ways the function is used. An interesting point to observe is that this function works in compatibility with non-numeric data as well as numeric.

However, it is not compatible with other arithmetical functions such as Sum (value1,2…), Min(value 1,2…), Divide(value 1,2,...), etc., when a textual value is entered, or the cell is left empty “ ” the result is 0 or #N/A. 

This gives the Averagea functions an advantage over other arithmetic functions.

The above image depicts the result of the sum/add function. The below image depicts the result when the Subtraction/Minus function is performed.

However, there are soo many functions under the Average tool to get the average range of values entered in the cell.

Remember, an Average is linked to the statistical aspect of the mathematical process. The main aim of an Average, in general, is to identify the mean of a range of data.

The arguments consist of numeric values, arrays, constant numbers, ranges, and cell references. However, this function does not work well with odd values standing out in the data set. For instance, let us assume data on some employees and their stipends:

As inferred in the above excel image, it can be seen that the stipend for Martha outliers that of other employees’ pay.

Hence, it can be concluded that the average returns calculated will result in inaccurate and unreliable data, as the average returns (the arithmetic mean) are highly influenced by Martha's stipend of $220,440,000.

The Steps for using AVERGEA function

Let us assume that we have to find the Average stipend paid to employees of a company:

Step 1: Enter the data into the excel sheet containing the numeric, non-numeric, and boolean values:

Step 2:  There are two ways to use the function:

• Method 1 In cell B13, type in the following syntax ‘=Average(B3:B12)’ and press Enter.
• Method 2 Keep the cursor on cell B13, and in the excel ribbon, select the Home tab, then choose AutoSum (∑) and opt for ‘Statistical’ and then select ‘Averagea’.

Step 3: After selecting the function from the AutoSum (∑), and statistics, cell B13 will display a syntax similar to that shown in the illustration as follows.

In the brackets, type the cell numbers that need to be used in the calculation, i.e. (B3:B13) in this case, and press enter. The cell will then display the average returns, which is 47,165.

The syntax will be shown in the cell after selecting the Averagea function from the list of Average under AutoSum (∑).

The above illustration depicts the values used in calculating Average returns (i.e., from Cell B3 to B12).

After entering the cells to be used in the calculation of the Average, the total Average comes out to be 47165. 

AVERAGEA Function In Multiple ranges of data set

This function also possesses the quality of calculating multiple ranges of data sets.

Let us understand this with the following illustrations and steps:

Step 1: Assign the values in the designated cells (numeric/ non-numeric/ boolean)

Step 2: Ensure that there are multiple ranges of data sets

Step 3: In the cell, type the syntax – “ = Averagea((B3:B8),(E3:E10)) and press ‘Enter’.

Let us take an example of evaluating a class’s average score over two semesters.

In this illustration, the scores of both semesters form the multiple ranges for the data set. The following are the steps that need to be carried out:

1. Form multiple data sets to carry out the function (i.e., semester 1 and semester 2)
2. In a different cell, type the following syntax – ‘=Averagea((B3:B8),(E3:E10))’ and Enter to get the average score of ‘81.46153846’. 
3. Don’t forget to insert the ‘comma’ between the data sets. 

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AVERAGE Function
• BINOM.DIST Function
• CHAR Function
• DAY Function
• ERROR.TYPE Function