Average Function in Excel (Formula, Examples)

AVERAGE Function in Excel

The AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function.

Below is the AVERAGE Formula.

Required: A number (of the range of numbers) for which the average is to be calculated.

number 1

Optional: Additional numbers (of the range of numbers) for which the average is to be calculated.

[number 2], [number 3], .. [number n]

These numbers can be given as input as– numbers, named ranges, ranges, or cell references that contain numeric values. The input can also be a result of other Excel operations outputting a number in the end. The AVERAGE in excel formula can handle a maximum of 255 individual arguments.

Returns: It returns the average of the supplied range of numbers. The cell references containing logical values, text or are empty are ignored by the AVERAGE in excel formula. However, the logical values or text representations of numbers entered directly are counted. If any of the supplied argument entered directly cannot be interpreted as numeric values, it gives #VALUE! error. If all the supplied set of arguments are non-numeric, it gives #DIV/0! Error. The arguments with error values also give an error.

Illustration

Suppose you want to find the average of {2, 3, 5, 4, 6}. These numbers are also given in cell B3:B7.

You can enter :

= AVERAGE ( B3: B7 )

It will return the mean i.e., 4 in this case.

You may also enter the numbers directly as:

= AVERAGE (2, 3, 5, 4, 6)

It will also return 4.

However, if you give the input as text as shown below:

= AVERAGE (“Two”, “Three”, “Five”, “Four”, “Six”)

It will give #VALUE! error.

If the input argument is cell references and none of them is a numeric value, as shown below:

=AVERAGE (A3:A7)

It will give #DIV/0! Error.

However, AVERAGE formula accepts the numbers in quotes as shown below:

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

=AVERAGE ( “2”, “3”, “5”, “4”, “6” )

It will return 4.

How to Use the AVERAGE Function in Excel?

The AVERAGE function in Excel is a statistical function and is one of the most used functions in Excel. In the financial sector, it is mostly used to calculate the average sales and the average revenue for a specific period of time.

Let us look at some of the examples of the AVERAGE function in Excel.

You can download this AVERAGE Function Excel Template here – AVERAGE Function Excel Template

Example #1

Suppose you have subject-wise marks of each student in a batch as shown below.

Now, you want to calculate the average marks of each student. To do so, you can use the AVERAGE formula for excel given below:

= AVERAGE (D4:H4)

for the first student and press Enter.

It will give the average marks obtained by the student Ashwin. Now, drag it to get the average marks of each of the student.

Example #2

Suppose you have the monthly sales data of your company. The data is divided into four different zones.

Now, you want

to calculate the average sales for each month.
to calculate the average sales for each zone
to understand in which zone the average sales is highest.

To calculate the average sales for each month, you could use the following AVERAGE Formula for Excel given below:

= AVERAGE (C4:F4)

which will give the average sales for Jan.

Similarly, drag it to get the average sales for the rest of the months.

To calculate the average sales for each zone, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (C4:C15)

for the East Zone and so on.

Now, you can also find which of the zone has the highest average. To do so, you can simply use the AVERAGE Formula for Excel given below:

= LOOKUP ( MAX(G18:G21), G18:G21, F18:F21)

Example #3

Suppose you have the marks for five subjects and you want to calculate the average marks scored by a student in the top four.

To simply calculate the average of the five subjects, you could use the AVERAGE Formula for Excel given below:

= AVERAGE (C4:G4)

However, to calculate the average of the top four marks, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (LARGE (C4:G4, {1, 2, 3, 4} ) )

It will give the average of the top four marks scored by the student of the five subjects and will return 83.

Similarly, drag it to get the average of top four for the rest of the students.

Example #4

Suppose you have some data containing both values and text given in a column (Column B here). Now, you want to calculate the average of the last three values of this column.

To find the average of the last 3 digits in the given data, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (LOOKUP (LARGE ( IF ( ISNUMBER (B3:B18), ROW (B3:B18) ), {1,2,3} ), ROW (B3:B18), B3:B18) )

and press CTRL + SHIFT + ENTER or COMMAND + SHIFT + ENTER (for Mac)

Let us look at the syntax in detail:

ISNUMBER (B3:B18) will check if the given input is a number and return logical value TRUE or FALSE. It will return : {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}
IF ( ISNUMBER (B3:B18), ROW (B3:B18) ) will filter the numerical values. It will return {3; 4; FALSE; 6; 7; 8; 9; FALSE; 11; 12; FALSE; 14; 15; FALSE; 17; 18}
LARGE ( IF ( ISNUMBER (B3:B18), ROW (B3:B18) ), {1,2,3} ) will give the three largest number. Here, it will return the last three positions of numerical values in the input. It will return {18; 17; 15}.
LOOKUP (.., ROW (B3:B18), B3:B18) will then return the corresponding values from {18; 17; 15} from B3:B18 and returns {50000; 90000; 110000}.
AVERAGE (..) will then give the average of the input.

Things to Remember

The AVERAGE function gives the arithmetic mean of the given range of numbers.
The input can be numbers entered directly, as cell references, or named ranges.
The numbers are added together and its sum is then divided by the total number of input numbers.
A maximum of 255 numbers can be supplied.
The cell references containing logical values, text or are empty are ignored by the AVERAGE function.
Cell references containing 0 are counted in the formula.
If any of the supplied argument entered directly cannot be interpreted as numeric values, it gives #VALUE! error.
If all the supplied set of arguments are non-numeric, it gives #DIV/0! Error.
The arguments with error values give an error.