What is the Excel AVERAGE Function?
The Excel AVERAGE function returns the average (arithmetic mean) of values supplied
AVERAGE Syntax
AVERAGE(number1, [number2], ...)
number1, required, number, cell or range containing numbers.
[number2], optional, another number, cell or range containing numbers, handles up to 255 arguments.Usage Notes:
- If you type directly into the argument list, logical values (TRUE/FALSE) and text representations of numbers are counted.
- The AVERAGE function count cell containing zero 0 values.
- The cell containing errors or unsuccessful conversion from text to the number will cause an error
- The AVERAGE function ignores cells containing logical values (TRUE/FALSE), text representations of numbers or empty cell.
Use Excel AVERAGEA function if you want to include logical values, text representations of numbers in the average calculation.
Use Excel AVERAGEIF function if you want to calculate the average that meets criteria, use Excel AVERAGEIFS function to calculate the average that meets more than one criteria.
How to Use AVERAGE Function in Excel
For example, there is data as shown below. What is the result of the AVERAGE function in column F?
AVERAGE Function #1
=AVERAGE(A2:E2)
Nothing special, the AVERAGE function calculates the five numbers in range A2:E2. The result is 3
AVERAGE Function #2
=AVERAGE(1,2,3,4,5)
Same as AVERAGE function #1, the difference is that all data is typed directly in the argument, not in the cell. The result is 3.
AVERAGE Function #3
=AVERAGE(A4:E4)
Cell A4 contains the logical values (TRUE), cell B4 contains alphanumeric “2”. The result is 4, where does the number 4 come?
Number 4 is obtained from the sum of 3 + 4 + 5 = 12 divided by 3. So, the AVERAGE function ignores logical values (TRUE) and alphanumeric “2” if stored in the cell.
AVERAGE Function #4
=AVERAGE(TRUE,"2",3,4,5)
The same data as AVERAGE function #3, but typed directly into the argument, the result is 3. The same data different placement, why can the results be different?
The AVERAGE function ignores logical values and alphanumeric if stored in the cell, but count both of them if typed directly in the argument.
The logical value TRUE converted to 1, whereas a logical value FALSE converted to 0. So the AVERAGE function #4 result is obtained by adding 1+2+3+4+5 = 15 divided by 5.
AVERAGE Function #5
=AVERAGE(A6:E6)
There are two texts, a text representation of numbers (cell A6) and a text (cell B6). Like in the AVERAGE function #3, all text will be ignored, excel only calculates the number in range C6:E6.
The result is 3.
AVERAGE Function #6
=AVERAGE("1","No Data",3,4,5)
The same data used in AVERAGE function #5 argument, but typed directly and different result.
The AVERAGE function ignores text if stored in the cell but try to convert to the number if typed directly in the argument; unsuccessful conversion returns a #VALUE! error.
AVERAGE Function #7
=AVERAGE(A8:E8)
The result is divide by zero error. The AVERAGE function returns the same error If there is an error in the argument. If there is more than one error, the AVERAGE function return that first appears error.
Please read the article below to find out how to solve divide by zero error. You can use the same method for other errors:
AVERAGE Function #8
=AVERAGE(A9:E9)
The result is 3.5, why?, because the AVERAGE function ignores empty cells, 2+3+4+5=14 divided by 4.
Please see the image below for the complete results of the AVERAGE function in column F.
