How To Calculate Average In Excel

5.8 min read|Last Updated: February 8th, 2024|Categories: excel|
table of content

The average is a typical exhibition of a data set, which is provided in the Excel spreadsheet in 4 functions for various situations. In this tutorial, we’re going to learn how to use these functions in Excel. 

In this blog (and many other articles) average and mean are used interchangeably. 

In mathematics, the average is the same as “arithmetic mean” or simply mean. To be more precise, it has to be stated that “mean” has several definitions, one of which i.e. arithmetic mean is equal to average. Geometric and harmonic mean are examples of different mean concepts and definitions.

The average is provided in the Excel in 4 functions.
Picture 1- The average functions in Excel

According to picture 1, the average functions include:

AVERAGE 

Which is used to calculate the arithmetic mean for a group of numbers.

The Set of data, which is one or more numeric values, is the number in the syntax of the function.

The Set of data is the number in the syntax of the function.
Picture 2- The syntax of the AVERAGE function

There are more ways to calculate the average:

  1. Use the AVERAGE function (according to video 1):
  1. Enter the AVERAGE function in an empty cell.
  2. Select your data as a range.
  3. Press Enter.
Video 1- The AVERAGE function
  1. Use the SUM function and divide by the COUNT function according to video 2:
  1. Enter the SUM function in an empty cell.
  2. Select your data as a range.
  3. Divide by the COUNT function.
  4. Select your data as range, again.
  5. Press Enter.
Video 2- Use the SUM and COUNT function to calculate the average
  1.  Add a quick calculation to your worksheet, automatically, such as the AVERAGE.
  1. Select an empty cell.
  2. Click on the FORMULAS tab.
  3. Click on the AUTOSUM.
  4. Select the AVERAGE from the list.
  5. Highlight your target data.
  6. Press Enter.

 

Add a quick calculation to your worksheet, such as the AVERAGE.
Picture 3- The AVERAGE function as a quick calculation

  1.  Use the QUICK ANALYSIS TOOL (Ctrl+Q) to quickly and easily analyze your data with some of Excel’s most useful tools, like the AVERAGE (according to video 3.)
  1. Select your data.
  2. Click on the QUICK ANALYSIS TOOL icon (at the bottom of the data.) or press Ctrl+Q.
  3. Select the TOTALS tab from the toolbar.
  4. Click on the AVERAGE.
Video 3- use the quick analysis tool to calculate the AVERAGE
  1.  Use the INSERT FUNCTION (Shift+F3), and pick the AVERAGE function from the STATISTICAL category (according to video 4.)
  1. Select an empty cell.
  2. Click on the FORMULAS from the ribbon.
  3. Click on the INSERT FUNCTION.
  4. Search the “AVERAGE” in the “search for a function” box or select the “Statistical” category from the “select the category” menu and select the AVERAGE in the “select a function” list. Then press OK.
  5. Enter your data (number 1, number 2, etc.) and press OK.
Video 4- Use the INSERT FUNCTION to calculate the AVERAGE

The AVERAGE relinquishes empty cells and cells that contain text (it doesn’t ignore zero values.)

You can calculate the average in excel 2007 and later, for up to 225 number arguments.

 

 

AVERAGEA

This function works like the AVERAGE. However, if you have cells that contain text or the logical value FALSE, the AVERAGE will assess it as zero. Also, the logical value TRUE is assessed as 1.

The syntax of the AVERAGEA function works like the AVERAGE.
Picture 4- The syntax of the AVERAGEA function

There is another way to calculate the AVERAGEA function; using the INSERT FUNCTION (according to video 5.)

  1. Select an empty cell.
  2. Click on the FORMULAS from the toolbar.
  3. Click on the INSERT FUNCTION.
  4. Search for the “AVERAGEA” in the “search for a function” box or select the “Statistical” category from the “select the category” menu, and select the AVERAGEA in the “select a function” list. Then press OK.
  5. Enter your data (value 1, value 2, etc.) and press OK.
Video 5- Use the INSERT FUNCTION to calculate the AVERAGEA

AVERAGEIF

This Excel function distinguishes the values in a range of cells and finds the average for them that meet the defined criteria. 

AVERAGEIF is used when you need to calculate the average of a data set with only one criteria in mind. For example, the average of numbers equal to or more than 10 or data related to a specific category. If you have more than one criterion, you will have to use the AVERAGEIFS function.

The AVERAGEIF function finds the average for them that meet the defined criteria.
Picture 5- The syntax of the AVERAGEIF function

AVERAGEIF calculate the average of a data set with only one criteria.
Picture 6- Usage of the AVERAGEIF function

  1. Select an empty cell.
  2. Enter “=AVERAGEIF” and press Enter.
  3. Define the “range.” (Defining the range depends on your criteria. Pay attention to picture 6 & 7)
  4. Define the “criteria.” (Enter your criteria among “ ”)
  5. Define the “average-range.”
  6. Press Enter.
An example for the Usage of the AVERAGEIF function.
Picture 7- Usage of the AVERAGEIF function (example 2)

Use the INSERT FUNCTION on the FORMULAS tab to calculate the AVERAGEIF. Follow these steps or watch video 6:

  1. Select an empty cell.
  2. Go to the FORMULAS tab and select the INSERT FUNCTION.
  3. Search for the AVERAGEIF or select the STATISTICAL category.
  4. Select the AVERAGEIF from the menu and press OK.
  5. Enter the RANGE, the CRITERIA, and the AVERAGE-RANGE. Then press OK.
Video 6- Use the INSERT FUNCTION to calculate the AVERAGEIF

The point is the AVERAGE-RANGE must include a numerical value. If your AVERAGE-RANGE contains text value or a blank cell, the result returns the #DIV/0! error.

AVERAGEIFS

A multiplex peer of the AVERAGEIF function is the AVERAGEIFS. You can specify 1 to 127 criteria, and calculate the average of the range which is tested against the criteria.

A multiplex peer of the AVERAGEIF function is the AVERAGEIFS.
Picture 8- The syntax of the AVERAGEIFS function

  1. Select an empty cell.
  2. Define the AVERAGE_RANGE.
  3. Determine the CRITERIA_RANGE1 and the CRITERIA1, the CRITERIA_RANGE2 and the CRITERIA2, …
  4. Press Enter.
An example for the Usage of the AVERAGEIFS function.
Picture 9- Usage of the AVERAGEIFS function

To use the INSERT FUNCTION option to calculate the AVERAGEIFS functions, follow the steps below or watch video 7:

  1. Select an empty cell.
  2. Go to the FORMULAS tab and select the INSERT FUNCTION.
  3. Search for the AVERAGEIFS or select the STATISTICAL category.
  4. Select the AVERAGEIFS from the menu and press OK.
  5. Enter the AVERAGE_RANGE, the CRITERIA_RANGE1 and the CRITERIA1, the CRITERIA_RANGE2, and the CRITERIA2,…. Then press OK.
Video 7- Use the INSERT FUNCTION to calculate the AVERAGEIFS

You can connect with us, ask our experts for your inquiries, and get more Excel Support Services.

Reduce costs, accelerate tasks, and improve quality with Excel Automation Services.

Our experts will be glad to help you, If this article didn't answer your questions.

Share now:

About the Author: Paria.S

Leave A Comment

contact us

Contact us today at and speak with our specialist.