What is the Excel AVERAGEA Function?
The excel AVERAGEA function calculates like the excel AVERAGE function.
The AVERAGE function and the AVERAGEA function return different results when there are logical values (TRUE / FALSE), empty text or text representations of numbers. The AVERAGE function ignores these values while the AVERAGEA function counts them.
AVERAGEA Syntax
AVERAGEA(value1, [value2], ...)
number1, required, number, cell or range containing numbers.
[number2], optional, another number, cell or range containing numbers, handles up to 255 arguments.Usage Notes:
- The AVERAGEA function count cells containing logical values (TRUE/FALSE), text representations of numbers or empty text.
- The AVERAGEA function evaluates TRUE as 1 and FALSE as zero.
- The AVERAGEA function evaluate text as 0 (zero) and empty text (“”) as 0 (zero).
How to Use AVERAGEA Function in Excel
For example, there is data as shown below. What is the result of the AVERAGEA function in column F. You can compare with the AVERAGE function result in column G?. Let’s see the difference.
AVERAGEA Function #1
=AVERAGEA(A2:E2)
Nothing special, all data in range A2:E2 is a number. The AVERAGEA function and the AVERAGE function return the same number.
The result is 3
AVERAGEA Function #2
=AVERAGEA(1,2,3,4,5)
The same data but typed directly in the argument list. The AVERAGEA function and the AVERAGE function still return the same number.
If all supplied value is a number, the AVERAGE and AVERAGEA functions return the same value; it doesn’t matter how the numbers are stored, typed directly in the argument list or stored in the cell.
AVERAGEA Function #3
=AVERAGEA(A4:E4)
Logical value TRUE in cell A4 and alphanumeric “2” in cell B4. The AVERAGEA and AVERAGE function return different results.
The AVERAGEA function return 2.6, obtained from the sum of 1+0+3+4+5=13 divided by 5. The AVERAGEA function converts TRUE to number 1 and convert all text to 0 (zero).
The AVERAGE function return 4, obtained from the sum of 3+4+5=12 divided by 3. The AVERAGE function ignores logical values (TRUE), and alphanumeric “2” when stored in the cell.
AVERAGEA Function #4
=AVERAGEA(TRUE,"2",3,4,5)
The same data as AVERAGEA function #3, but typed directly into the argument, the result is 3. The same data different placement, why can the results be different?
Fungsi AVERAGEA count logical values and alphanumeric. There is no different behavior for logical values, wherever it is located excel convert TRUE to number 1 and FALSE to number 0. For alphanumeric, Excel converts it to the number if stored in the argument list and consider it as number 0 when stored in the cell.
The AVERAGE function would do the same thing if all supplied values typed directly in the argument list. The AVERAGE function returns the same number as the AVERAGEA function
AVERAGEA Function #5
=AVERAGEA(A6:E6)
Cell A6 contains an alphanumeric and cell B6 contain a text. The AVERAGEA function counts all text but treats it as zero. 0+0+3+4+5=12 divide by 5, the result is 2.4.
The AVERAGE function ignores all text stored in the cell. 3+4+5=12 divide by 3, the result is 4.
AVERAGEA Function #6
=AVERAGEA("1","No Data",3,4,5)
Just like the AVERAGE function, the AVERAGEA function will try to convert all text typed directly in the argument list to a number, if fail return an error.
Excel failed to convert the “No Data” text; the result is a #VALUE! error.
AVERAGEA Function #7
If there is an error in the AVERAGEA argument, then the same error will be the result. The first appears error will be the result if more than one error exists in the supplied values.
Please read the article below to find out how to solve divide by zero error. You can use the same method for other errors:
AVERAGEA Function #8
=AVERAGEA(A9:E9)
Why the AVERAGEA and AVERAGE result different?. Cell A9 is not an empty cell but an empty text. The AVERAGEA function counts an empty text while the AVERAGE function ignores it.
The results of the AVERAGEA function are obtained from the sum of 0+2+3+4+5=14 divided by 5; the result is 2.8.
while the result of the average function is 3.5 obtained from the sum of 2 + 3 + 4 + 5 = 14 divided by 4, the result is 3.5.
Please see the image below for the complete results of the AVERAGEA function in column F and AVERAGE function di column G
