AVERAGE Function in Excel

Excel AVERAGE Function (Table of Contents)

• AVERAGE Function in Excel
• Formula
• Illustration
• How to Use?

AVERAGE Function in Excel

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

AVERAGE in Excel Formula

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.

AVERAGE Function in Excel – 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 are 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:

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

It will return 4.

How to Use 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.

AVERAGE in Excel Formula 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.

AVERAGE in Excel Formula Example #2

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

Now, you want

1. to calculate the average sales for each month.
2. to calculate the average sales for each zone
3. 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)

AVERAGE in Excel Formula 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.

AVERAGE in Excel Formula 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.

Recommended Articles

This has been a guide to AVERAGE Function in Excel. Here we discuss the AVERAGE Formula in excel and how to use AVERAGE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• Weighted Average Excel
• Excel Data Filters can be used for VBA
• FIND Formula on Excel
• ROW Excel Function
• LARGE Function in Excel
• ISNUMBER Function
• MONTH Formula on Excel
• Weighted Average Formula
• True Excel Function
• INDIRECT Function in Excel
• SUMPRODUCT Excel Function