Excel Functions Tutorials

- Statistical Function in Excel
- AVERAGE Excel Function
- Average Formula in Excel
- AverageIF in Excel
- AVERAGEIFS Function in Excel
- CORREL Excel Function
- Correlation Matrix in Excel
- Correlation vs Covariance
- COUNT Excel Function
- Count Formula in Excel
- COUNTA Excel Function
- COUNTIF Excel Function
- COUNTIF Formula in Excel
- COUNTIFS Function in Excel
- COUNTIF with Multiple Criteria
- COUNTIF Examples
- FORECAST Excel Function
- Forecast Formula in Excel
- FREQUENCY Excel Function
- Frequency Formula in Excel
- GROWTH Excel Function
- Growth Formula in Excel
- LARGE Excel Function
- LINEST Excel Function
- Linear Regression in Excel
- Lognormal Distribution in Excel
- MAX Excel Function
- Max Excel Formula
- Mean vs Median
- MEDIAN Excel Function
- MEDIAN Formula in Excel
- MIN in Excel
- MODE Excel Function
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- PERCENTILE Excel Function
- Percentile Formula in Excel
- Percentile Rank Formula
- Poisson Distribution in Excel
- P-Value in Excel
- QUARTILE Excel Function
- RANK Function in Excel
- SLOPE Function in Excel
- SMALL Function in Excel
- Standard Deviation in Excel
- Standard Deviation Formula in Excel
- TREND Function in Excel
- T-TEST in Excel
- Chi Square Test in Excel
- Variance vs Standard Deviation
- Weibull Distribution in Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)

Related Courses

**Excel AVERAGE Function (Table of Contents)**

## 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:

4.9 (1,353 ratings)

=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

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

### 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.

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

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion