How to Calculate Average in Excel? (with Images)

Excel has provided some particular functions to calculate average. Like it has AVERAGE, AVERAGEA, AVERAGEIF functions which returns the average of the numbers fed to the functions in the argument. The average is the arithmetic mean of the considered numbers.

In this section, we will discuss different methods to calculate the average. We will also discuss the different average functions provided by Excel. The syntax of the AVERAGE function is as follow:

AVERAGE(number1, [number2], …)

number1 is the first number of which you need average.

number2 is an optional number which if present is reviewed for computing the average.

Maximum 255 numbers can be fed as an argument to the AVERAGE function.

Steps to Calculate Average in Excel 2016:

Manually Calculating Average in Excel

The example below shows the calculation of the average manually. It is simply the addition of the numbers, divided by the count of that numbers.

Using Other Excel Formulas to Calculate Average

For calculating the average, we have used two excel functions SUM and COUNT. The SUM function returns the addition of all the numbers from A2 through A7 (in this example) which is then divided by the count of numbers from A2 through A7 returned by the COUNT function. This formula returns the average of the numbers from cell A2:A7.

AVERAGE Function in Excel

AVERAGE() is an Excel function specifically for calculating the average of the numbers fed to the function in arguments. In the example below, the AVERAGE() function calculate the average of the numbers from cell A2 through A7.

The AVERAGE() function do not consider the logical values like TRUE or FALSE, text or an empty cell while calculating the average of the numbers. Like in the example below, the AVERAGE() function calculate the average of three numbers only i.e. 19, 25, 18 and ignore the cells with logical values, text & empty cells.

You can also manually feed the numbers to AVERAGE() function as shown in the example below.

The AVERAGE() function also accepts cell range along with the numbers in its argument as shown in the image below.

AVERAGEA() Function in Excel

The AVERAGEA function in Excel calculates the average of the numbers fed to it in the argument. Along with this it also considers logical values and cells containing texts. But, AVERAGEA function does not consider the empty cells.

AVERAGEA function considers ‘0’ for logical value FALSE and cell containing text value. And for the logical value TRUE it takes 1.

In the example below, the AVERAGEA function calculates average as (19+25+0+18+1+0)/6 i.e. the cell value for ‘FLASE’ & text ‘Jhon’ is taken 0 and for TRUE it is considering 1.

AVERAGEIF() Function in Excel

The AVERAGEIF function also returns the average of the numbers based on the conditions specified in the argument. The syntax of the AVERAGEIF function is as follow:

AVERAGEIF(range, criteria, [average_range])

range: is the number of cells to average, if the average_range is not present.

criteria: is the condition evaluated to find which cells are to be averaged.

average_range: the actual cells that are averaged.

In the example below, you can observe that the AVERAGEIF function calculate the average of those number of students whose favourite fruit is not equal to Grapes.