WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » AVERAGE Function in Excel

AVERAGE Function in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

AVERAGE Formula

Required: A number (of the range of numbers) for which the average is 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.

AVERAGE Function Illustration 1

You can enter :

= AVERAGE ( B3: B7 )

AVERAGE Function Illustration 2

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

AVERAGE Function Illustration 3

You may also enter the numbers directly as:

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

AVERAGE Function Illustration 4

It will also return 4.

Illustration 5

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

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

Illustration 6

It will give #VALUE! error.

Illustration 7

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

=AVERAGE (A3:A7)

Illustration 8

It will give #DIV/0! Error.

Illustration 9

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

Popular Course in this category
Sale
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

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

Illustration 10

It will return 4.

Illustration 11

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.

Example 1

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)

AVERAGE Function Example 1-1

for the first student and press Enter.

Example 1-2

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 1-3

Example #2

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

AVERAGE Function Example 2

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)

AVERAGE Function Example 2-1

which will give the average sales for Jan.

AVERAGE Function Example 2-2

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

AVERAGE Function Example 2-3

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.

AVERAGE Function Example 2-4

AVERAGE Function Example 2-5

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 Function Example 2-6

AVERAGE Function Example 2-7

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.

AVERAGE Formula Excel Example 3

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

= AVERAGE (C4:G4)

AVERAGE Formula Excel Example 3-1

AVERAGE Formula Excel Example 3-2

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} ) )

AVERAGE Formula Excel Example 3-3

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

AVERAGE Formula Excel Example 3-4

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

AVERAGE Formula Excel Example 3-5

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.

AVERAGE Formula Excel Example 4

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) )

AVERAGE Formula Excel Example 4-1

AVERAGE Formula Excel Example 4-2

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.

AVERAGE Function in Excel Video

Recommended Articles

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

2 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>