AVERAGEA Function in Excel – Finding Average Value From All

In this blog, we would learn how to use the AVERAGEA function in Excel to find the mean numerical value from an array.

Here we go 😎

When To Use AVERAGEA Function in Excel

The word “AVERAGEA” represents Average Of All. The AVERAGEA formula in excel helps us to find the mean or average of an array of elements. The group of cells can contain numerical values, logical values (TRUE or FALSE), text strings, dates, timings, or blank cells.

AVERAGEA function is one of the useful statistical inbuilt functions of excel.

Points To Know About Excel AVERAGEA Formula

The following points should be kept in mind before using the AVERAGEA formula.

• For the AVERAGE function, text string means 0. It denotes that, the AVERGAE formula in excel will consider the value of cell containing a text string as zero (0).

• Similarly, logical TRUE and FALSE return 1 and 0 respectively.

• The function would ignore the blank cells.

• The function can have maximum of 255 arguments.

• If you want to ignore the logical values (i.e. TRUE or FALSE) / text strings , then use excel’s AVERAGE function.

• Unlike AVERAGE function, AVERAGEA function will not give an error if we pass only logical values or text strings as the function argument.

• We can use AVERAGEA function with ROUND function of excel, to round off the result of AVERAGEA to specified decimal places. For instance, check example 2 of AVERAGE formula.

Syntax and Arguments

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

The following points define the function arguments for the AVERAGEA function.

• value1 – This can be a cell refrence, range, value, constant, cell having text string, logical values( TRUE or FALSE) or a blank cell.

• [value2] – This is an optional argument, and accepts same values as value1.

Examples to Learn Excel AVERAGEA Formula

Let us now learn this formula with some of the examples for the AVERAGEA function of Excel.

Ex. 1 – Simple Example to Learn AVERAGEA Function

In this example, we let us consider the marks of students in different subjects.

Enter the below formula in cell D2 to get the average marks for the first record.

=AVERAGEA(A2:C2)

As a result, the function has returned 42 in cell D2.

Explanation – The function has successfully calculated the mean of the range A2:C2.

Finally, copy the formula in cell D2 and paste it to other cells in column D.

Ex. 2 – Passing Multiple Arguments To AVERAGEA Function Of Excel

Let us now learn how to use multiple arguments of excel AVERAGEA function.

In this example, we will pass on three arguments – a cell range, a cell, and a constant value 10.

To find the average of values in range A2:A5, cell C2, and constant 10, use the following formula.

=AVERAGEA(A2:A5,C2,10)

As a result, excel will return the output value as 15.

Explanation – We have passed a range A2:A5, a cell C2, and a constant 10 as the function argument. The function has calculated the average of the numerical values contained in range A2:A5 i.e 10, 20, 20, 10, cell C4 i.e 20, and the constant 10. There are 6 numerical values and their average is 15.

Ex. 3 – Difference between AVERAGEA and AVERAGE Function in Excel

Now, this is high time, to know the main difference between the working of AVERAGE and AVERAGEA function of Excel.

In this example, we will first use the AVERAGEA function to see the values and their actual considerations by the AVERAGEA Formula.

Enter the following formulas to see the result when the same arguments are passed to AVERAGE and AVERAGEA function of excel.

=AVERAGEA(A2:A8)

=AVERAGE(A2:A8)

As a result, the AVERAGEA function has returned 8.714 while the AVERAGE function has returned 15.

Explanation – The same range A2:A8 passed to both of the functions. The green rectangle represents the actual considerations by AVERAGEA function while the orange rectangles represent the actual considerations by AVERAGE function. So the two functions work differently as their results are in cells C11 and C14 respectively.

Finally, you have reached the end of this blog and achieved a great milestone. Keep it up and keep reading.

Thank you 🙂