Average Function in Excel – Formula and Examples

The AVERAGE function is a popular MS Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient way to analyze and summarize numerical data. The article 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. Let's explore each of the options available in Excel for calculating averages.

Content

• What is the AVERAGE function in Excel?
• Formula of the AVERAGE function
• How to Apply the AVERAGE Function? Example 1
• How to Apply the AVERAGE Function? Example 2
• Calculate Average with a Condition (AVERAGE.IF)
• AVERAGE.IF Example
• Important to Note

What is the AVERAGE function in Excel?

MS Excel’s AVERAGE function is a statistical function. The average formula in Excel calculates the arithmetic mean of a data series. It adds all the values ​​of the variable 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.

Example 1 – How to Apply the AVERAGE Function?

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

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

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

Step 2. Select the data you want to add. In this case, the selected cell numbers are from C2 to C9.

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

Example 2 – How to Apply the AVERAGE Function?

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 –

Step 1. Apply the average function at the end of the table, row 11, here.

Step 2. Close the parentheses and press Enter to get the result as above.

Step 3. 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.

Step 4. Once you hold and drag toward the following cells, the Average function fills each cell for the Average of the available data.

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

To get the non-formatted data, you can copy and paste the entire value as Values (V).

Now you have clean data without any formatting 

Calculate Average with a Condition (AVERAGE.IF)

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

The AVERAGEIF function helps us find the cells’ average (arithmetic mean) that meets 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 cells will be used to find the average. If omitted, the cells in the range calculate the average. 

Example – AVERAGE.IF

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

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

Step 1. Start writing our formula with the equal sign (=).

Step 2. 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”. 

Step 3. Select the range of values ​​where the average applies if it meets the criteria.

The screenshot below shows that the AVERAGEIF function evaluates the sales average greater than $50,000. 

Coming to our second requirement, 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).

Step 4. 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 the 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 no cells in the range 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. Understanding the Average and AVERAGE.IF functions in Excel are essential for efficient data analysis, allowing you to calculate averages and apply conditions quickly. Practising with real datasets will help reinforce these concepts and improve your Excel skills. Stay tuned for more tutorials in this series to further build your knowledge of Excel functions.