Average Function in Excel – Formula and Examples

Average Function in Excel – Formula and Examples

5 mins read39.7K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 7, 2023 14:13 IST

The AVERAGE function is a popular 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. We will see one by one each of the options in Excel Average.

2022_03_AVERAGE-AVERAGEIF-Functions-2.jpg

Content

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 1How 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.

2022_03_Average-Function-Example-1-.1.jpg

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

2022_03_Average-Function-Example-1.2.jpg

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

2022_03_Average-Function-Example-1.3.jpg
150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more
How to Divide in Excel?
How to Divide in Excel?
Division is one of the four basic arithmetic operations, and it is a common operation performed in Excel. To divide in Excel, you can use the forward slash (/) operator....read more
How to Multiply in Excel?
How to Multiply in Excel?
Learn how to multiply in Excel using different criteria. Understand through different examples.
 

Example 2How to Apply the AVERAGE Function?

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 –

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

2022_03_Average-Function-Example-2.1.jpg

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.

2022_03_Average-Function-Example-2.2.jpg

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

2022_03_Average-Function-Example-2.3.jpg

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

2022_03_Average-Function-Example-2.4.jpg

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

2022_03_Average-Function-Example-2.5.jpg

Now you have clean data without any formatting 

2022_03_Average-Function-Example-2.6.jpg
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.

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. 

ExampleAVERAGE.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”. 

2022_03_Calculate-Average-with-a-Condition-AVERAGE.IF_.jpg

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

2022_03_Calculate-Average-with-a-Condition-AVERAGE.IF-2.jpg

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

2022_03_Calculate-Average-with-a-Condition-AVERAGE.IF-3.jpg

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

2022_03_Calculate-Average-with-a-Condition-AVERAGE.IF-4.jpg

Step 4. Press Enter

2022_03_Calculate-Average-with-a-Condition-AVERAGE.IF-5.jpg

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. We recommend you practice using some datasets to enhance your understanding and gain practical Excel average knowledge. We will continue this series of tutorials on MS Excel functions.

 

FAQs

What is the average function?

The AVERAGE function is a statistical function. It is used to analyze the range of data and find the average value. where argument-list is a list with no more than 255 numeric values.

What is the use of the average function in Excel?

An average is a representative number that can be obtained from a list of figures. It is usually related to the concept of the arithmetic mean. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

How to use the average function in Excel?

To use the AVERAGE function in Excel -

  • Click on the cell where you want the result of the operation to appear
  • Write =AVERAGE; Select the range of cells from which you want to calculate the average
  • Press ENTER and the result of the function will appear in the cell.

What is the AVERAGE IF function in Excel?

The AVERAGE IF function returns the average of the cells in a range that meet the criteria you specify.

How to get the average of a sum in Excel?

Use AutoSum to quickly calculate the average

  • Click in a cell below the column or to the right of the row of numbers whose average you want to average.
  • On the Home tab, click the arrow next to Sum > Average
  • Press Enter.
About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments