AVERAGE function in Excel
The AVERAGE function in Excel is used to find the arithmetic mean of specified numbers. The syntax is as follows:
Where number1, number2, etc. are numeric values for which you want to get the average. They can be supplied in the form of numeric values, arrays, cell or range references. The first argument is required, subsequent ones are optional. In one formula, you can include up to 255 arguments.
AVERAGE is available in all versions of Excel 365 though Excel 2007.
AVERAGE function - 6 things to know about
For the most part, using the AVERAGE function in Excel is easy. However, there are a few nuances that you should be aware of.
- Cells with zero values are included in the average.
- Empty cells are ignored.
- Cells containing text strings and logical values TRUE and FALSE are ignored. If you want to include Boolean values and text representations of numbers in the calculation, use the AVERAGEA function.
- Boolean values typed directly in a formula are counted. For example, the formula AVERAGE(TRUE, FALSE) returns 0.5, which is the average of 1 and 0.
- If the specified arguments do not contain a single valid numeric value, a #DIV/0! error occurs.
- Arguments that are error values cause an AVERAGE formula to return an error. To avoid this, please see how to average ignoring errors.
Note. When using the AVERAGE function in your Excel sheets, please do keep in mind the difference between cells with zero values and blank cells - 0's are counted, but empty cells are not. This might be especially confusing if the "Show a zero in cells that have a zero value" option is unchecked in a given worksheet. You can find this option under Excel Options > Advanced > Display options for this worksheet.