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

**Average Formula in Excel (Table of Contents)**

## Excel Average Formula

The AVERAGE formula in Excel returns the average or arithmetic mean of numerical values supplied as multiple parameters or arguments. The input/arguments can also be a result of other Excel operations outputting a number in the end.

It can take a maximum of 255 individual arguments, which can include cell references, numbers, arrays, ranges, and constants. It returns a numeric value as the average.

General Syntax for AVERAGE formula in excel is as follows:

**AVERAGE (argument1, [argument2],.…….) **

The AVERAGE formula syntax has the following arguments:

**Argument1:**Required, represents the cell reference, number, or range for which average is desired**Argument2:**Optional, represents the cell reference, number, or range for which average is desired

**Steps to calculate Average using Formula Bar in Excel**

**#1 –** Click on the cell where the average is to be computed, then type ‘=’ followed by ‘AVERAGE’, and enter the arguments for which average is desired.

**#2 –** Under the ‘Formulas’ bar, there is an icon of ‘Insert Function’. On clicking this, a dialog box appears that shows a list of functions.

**#3 –** Select the function ‘Average’ and then click ‘OK’. On clicking ‘OK’,

**#4 –** Another dialog box appears that asks for arguments to be entered in the function. Enter the arguments and then click ‘OK’.

Under the ‘Formulas’ bar, there is an icon of ‘AutoSum’. On clicking this dropdown of AutoSum, click ‘Average’ from the menu. This option can be used when data is in the same row or column.

4.9 (1,353 ratings)

The Average formula applies.

But when this is not the case, then we can also find Average function on ‘More Functions’ under the ‘Formulas’ bar. Then click ‘Statistical’ and click on ‘Average’.

Enter the arguments and then click ‘OK’.

**How to Use Average Formula in Excel?**

Following are the ways with which excel Average Function can be used.

#### Example #1

If we wish to calculate an average of five values that are stored in cells A2:A6:

=AVERAGE(A2: A6)

We can see that the formula returns an average of non-blank numerical cells in the range: A2: A6.

#### Example #2

Now, let’s say in the above example we enter the numbers directly as arguments instead of providing cell range as an argument:

=AVERAGE(2,3,8,7,3)

We can see that the formula returns average of numerical values provided in the function and returns the same value as in the above example.

#### Example #3

Now, let’s say in Example1 we enter the range or array as individual cell references as arguments, then the excel AVERAGE formula would work as follows:

=AVERAGE(A2,A3,A4,A5,A6)

** **

The result is shown below.

#### Example #4

Let’s say that we wish to calculate the average marks of each student when we have their subject-wise marks, then the AVERAGE function would work as follows::

=AVERAGE(B2: G2) =AVERAGE(B3: G3), =AVERAGE(B4: G4), =AVERAGE(B5: G5), =AVERAGE(B6: G6)

Now we apply the average formula

The result is shown below:

We can see that the AVERAGE formula above returns the average marks of five subjects obtained by student Raj. On dragging this from cell H2: H6, we can get average marks of each student.

#### Example #5

If we wish to calculate the average of numbers stored in cells A2: A6 and the number 6:

=AVERAGE(A2:A6,6)

The result is shown below.

#### Example #6

Let’s say that we wish to calculate average when the parameters are a mix of manually typed numbers and ranges, then the AVERAGE function would work as follows:

=AVERAGE(A2:A6,6,B2,8)

The result is shown below.

#### Example #7

If we wish to calculate the average of numbers that are not in a contiguous row or column:

=AVERAGE(A2:A6,A9)

The result is shown below.

**Things to Remember**

- AVERAGE measures central tendency, which is the location of the center of a group of numerical values in a statistical distribution.
- AVERAGE is a built-in formula in Excel and is categorized as a statistical function.
- In the formula, the numbers are added together and its sum is divided by the total number of input values.
- The AVERAGE function ignores blank/empty cells.

We can see in the above two screenshots that the two formulas: AVERAGE (A2: A6), and AVERAGE (E2: E5) give the same result, i.e both of them return the same value ‘3.75’ when the range A2: A6 contains an empty cell. So, we can see that empty cells are ignored by AVERAGE function, with the two averages being calculated as follows:

**Average=(2+3+7+3)/4=15/4=3.75**

- Cell references containing text or logical values are ignored by the AVERAGE formula in Excel.

Following screenshot illustrates this:

We can see in the above screenshot that when cell reference entered as the parameter in function contains the text: ’Two’ and logical value: ‘TRUE’, then they are ignored by AVERAGE function, with the average being calculated as follows:

**Average= (3+5+3)/3=11/3=3.66667**

However, if the logical values or text representations of numbers are entered directly as arguments, then they are treated as numerical values and are counted.

Following screenshot illustrates this:

We can see in the above screenshot that when two logical values TRUE and FALSE are entered directly as arguments in AVERAGE function, then they are counted as numerical values and an average is computed with TRUE being considered as 1, and FALSE being considered as 0, so average=((1+0)/2)=0.5.

We can see in the above screenshot that AVERAGE excel formula accepts numbers in quotes and average is computed with these considered as numbers.

- Cells with zero value are also counted. We can see this in the below screenshot:

In this case, the average is calculated as follows:

**Average= (2+3+7+0+5)/5=17/5=3.4**

- If any of the parameter entered in the function directly cannot be interpreted as numeric values, then the AVERAGE function returns #VALUE! Error. We can see this in the below screenshot:

- If all the parameters entered in the function are non-numeric, then the AVERAGE function returns #DIV/0! Error. We can see this in the below screenshot:

- Parameters with error values also return an error. We can see this in the below screenshot:

We can see in the above screenshot that the AVERAGE Excel formula returns an error (#VALUE!) since the value in cell G6 contains an error value (#N/A).

- If we wish to include logical values and text representations of numbers in a reference as part of the calculation, then the AVERAGE function can be used.
- If we wish to calculate the average of only those values that meet certain criteria, then AVERAGEIFS or AVERAGEIF function can be used.

### Recommended Articles

This has been a guide to AVERAGE Formula in Excel. Here we discuss how to use the Average Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

- VBA IF OR
- Examples of Weighted Mean Formula
- DIR Function in VBA
- Average Formula
- Top Basic Excel Formulas
- Mode Function in Excel
- Mean Formula

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