Benefits of AVERAGE function in Excel

Calculate the arithmetic mean of a given set of arguments

Inhaltsverzeichnis Show

=AVERAGE(number1, [number2], …)
How to use AVERAGE Function in Excel?
A few notes about the AVERAGE Function
Additional Resources
Article Sources
What is the AVERAGE function in Excel?
Formula of the AVERAGE function
=AVERAGE (number1, [number2], …)
How to Apply the AVERAGE Function? Example 1
How to Apply the AVERAGE Function? Example 2
Calculate Average with a Condition (AVERAGE.IF)
The AVERAGE.IF Example
Important to Note

The AVERAGE Function[1] is categorized under Excel Statistical functions. It will return the average value of a given series of numbers in Excel.

The function is used to calculate the arithmetic mean of a given set of arguments in Excel. This guide will show you, step-by-step, how to calculate the average in Excel.

As a financial analyst, the function is useful in finding out the average (mean) of a series of numbers. For example, we can find out the average sales for the last 12 months for a business.

Formula

=AVERAGE(number1, [number2], …)

The function uses the following arguments:

Number1 (required argument) – This is the first number of a cell reference or a range for which we want the average.
Number2 (optional argument) – They are the additional numbers, cell references or a range for which we want the average. A maximum of 255 numbers is allowed.

How to use AVERAGE Function in Excel?

To understand the uses of the AVERAGE function, let us consider a few examples:

Example 1 – Average in Excel

Suppose we are given the following data:

We wish to find out the top 3 scores in the above data set. The formula to use will be:

We get the result below:

In the above formula, the LARGE function retrieved the top nth values from a set of values. So, we got the top 3 values as we used the array constant {1,2,3} into LARGE for the second argument.

Later, the AVERAGE function returned the average of the values. As the function can automatically handle array results, we don’t need not use Ctrl+Shift+Enter to enter the formula.

Example 2 – Average in Excel

Suppose we are given the data below:

The formula to use is shown below:

We get the following result:

A few notes about the AVERAGE Function

The AVERAGE function ignores empty cells.
If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors in the function.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to the Excel AVERAGE function. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

Article Sources

In the article, we will cover how to use the AVERAGE function and AVERAGE.IF function in Excel. In addition to automatically calculating the average of a series of data, Excel allows us to enter one or several conditions to calculate the average. We will see one by one each of the options.

Content

What is the AVERAGE function in Excel?

MS Excel’s AVERAGE function is a statistical function. The average function calculates the arithmetic mean of a series of data. That is, it adds all the values of the variable that we are analyzing and divides them by the number of values added.

Formula of the AVERAGE function

=AVERAGE (number1, [number2], …)

The AVERAGE function has the following arguments

Number1 – Required. The first number, cell reference, or range for which you want to calculate the average.

Number2 – Optional. Additional numbers, cell references, or ranges for which you want to calculate the average. The max count is 255.

How to Apply the AVERAGE Function? Example 1

In this example we will learn to use the average function across a set of continuous cells, commonly referring to a column or row of data.

Given the following example with the number of vehicles used by the Transportation department of a certain company. The data is represented in a column.

Start writing =AVERAGE( in the cell where you want the result to appear.

You can now select the data you want to add. In this case, the selected cell numbers are from C2 to C9.

Close the parentheses and press Enter to see the result in cell C10.

How to Apply the AVERAGE Function? Example 2

Now we will add more data to the above sheet. Now we have the vehicular data for the years 2018-21.

To take out the average of the vehicles used every year, we will apply the average function at the end of the table, row 11 here.

Now we will close the parentheses and press Enter to get the result as above. To pull the average of every column, you would need to hold the cell where you have got the result and drag it to the cell for which you want to calculate the average.

Once you hold and drag towards the next cells the Average function fills each cell for the Average of the available data.

Extra Tip – In the above data you can see a formatting sign. You would need to clean your data here.

Now you can copy the entire value and paste it as Values (V) to get the non-formatted data.

Now you have the clean data, without any formatting

Calculate Average with a Condition (AVERAGE.IF)

Through the Excel spreadsheet, we can also calculate the average according to a given condition or criteria. This is possible to do through the AVERAGEIF function.

The AVERAGEIF function helps us find the average (arithmetic mean) of the cells that meet a certain condition.

AVERAGEIF Function Syntax

AVERAGE.IF(range, criterion, [average range])

Range: The cell range in which the criteria will be searched

Criteria: Is the condition or criteria in the form of a number, expression, or text that determines which cells will be used to find the average.

Average_range: These are the cells that will be used to find the average. If omitted, the cells in the range calculate the average.

The AVERAGE.IF Example

Below is a classic example of sales of a product against a set of orders for different employees.

To calculate the average sales greater than $50,000, we are going to use the AVERAGEIF function.

We start writing our formula with the equal sign (=).

We will then choose the range where our criterion is, which for our example is in B2:B7. The criteria or condition of our formula, placed in quotes, is “>50000”.

Finally, we choose the range of values where the average applies if it meets the criteria.

In the below screenshot, you can see that the AVERAGEIF function evaluates the average of sales greater than $50,000.

Now coming to our second requirement where we have to calculate the average of orders greater than 50.

Here one range of cells is evaluated against the criteria (here B2:B7) and the second range of cells is average (C2:C7).

Press Enter

Important to Note

Range cells containing TRUE or FALSE are ignored
If a cell in average_range is an empty cell, AVERAGEIF ignores it
If range is a blank or text value, AVERAGEIF returns the #DIV0! error value
If a criteria cell is empty, AVERAGEIF treats it as a 0 value
If there are no cells in the range that meet the criteria, AVERAGEIF returns the #DIV/0! error value

Hope this tutorial on Average and AVERAGE.IF Functions in MS Excel helped you to understand the concept. We recommend you practice using some datasets to enhance your understanding and gain practical knowledge. We will continue this series of tutorials on MS Excel functions.

Top Trending Articles: