Average Function in Excel

Posted on:  06/07/2020
Average Function in Excel

The AVERAGE function is one of the most popular functions in Excel. This video shows how to use the Average function, handles zero values when using the Average function. Zero values vs. blank cells will give different results with Average. As a bonus, using the AverageIF function with zero values.

The Average Function returns the average (arithmetic mean) of the arguments. For example, if the range B2:B20 contains numbers, the formula =AVERAGE(B2:B20) returns the average of those numbers.

Syntax of the AVERAGE function

AVERAGE(number1, [number2], ...)

The AVERAGE function syntax has the following arguments:

Number1    Required. The first number, cell reference, or range for which you want the average.

Number2, ...    Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

Zero values with the Average Function

A value of zero is calculated when using the average function. Zero values and blanks will give you different answers as shown below.

One thing to consider when you have zero values is the Excel option Show a zero in cells that have zero value. When this is unchecked, the zero values are still calculated into the Average, but they don't' show on the spreadsheet and appear as blanks. When you click on a value, you will see a zero in the formula bar.

YouTube video on the Average function