Javatpoint Logo
Javatpoint Logo

How to calculate average in Excel

Average is accepted universally to represent a wide set of numbers with a single number (usually arithmetic mean). Excel possesses inbuilt AVERAGE functions to calculate the arithmetic mean. The Excel AVERAGE function gives an output representing a number that could be a range, distribution, or list of numbers. Average is determined by adding all the specified numbers, then dividing the sum by the total number of values.

In this tutorial, we will cover the various Excel Average Functions, their syntax and will calculate the Average of different numbers with the help of examples. Below are the methods

  • AVERAGE
  • AVERAGEA
  • AVERAGEIF

1. Excel AVERAGE Function

The Excel AVERAGE function calculates the average of the numbers in the specified cell by adding all the given arguments and dividing the sum by the total number of values.

Note: The AVERAGE function can hold only up to 255 arguments where the parameter can be a range, value, or even a cell reference. Only one argument is required with this function, but two or more are always preferred.

Syntax

Where the arguments (num1, num2, num3..) can be a value, range or even a cell reference.

Steps to Calculate the Average

  1. Enter the data in your Excel worksheet. In the given sheet, as you can see we have data in B2, B3, B4, B5 and B6 cells.
    How to calculate average in Excel
  2. Next, to find the Average go to the B7 cell and type the Average formula. Start the formula with equal to (=) followed with Average. In the arguments, we will pass the cell reference, take your cursor to the B2 cell, and drag the mouse till B6. It will select all the cells and point to the range of cells, i.e., Average (B2:B6).
    How to calculate average in Excel
  3. Press the Enter button on your keyboard to get the average of the selected number. You will have the following output:
    How to calculate average in Excel
  4. Though in the above example, we have calculated the average of the contiguous cells, you can select non-contiguous (non-adjacent) cells. Type the function and for the arguments hold the Control key and select the non-contiguous cells.

NOTE: The non-contiguous cells are separated by commas.

How to calculate average in Excel

Points to Remember

  • Average Function ignores text values and empty cells.
How to calculate average in Excel

In the above dataset you will notice we have a text value in B3 cell ("Fail") and an empty value in B6 cell. But none of this will affect your average function as it ignores those cells and looks forward for numeric values and gives you the output.

  • Average Function doesn't include Zero values.

When calculating the average, the sum of the values is divided by the number of values. Though zero won't affect the sum, they will be considered valid figures while calculating the total values. Hence it will reduce the overall average and, therefore, will give you a wrong output.

2. Excel AVERAGEA Function

Another way to find the average in Excel is by using the AVERAGEA function. This function is used to eliminate this miscalculation of AVERAGE (because in AVERAGE function the text values are ignored and are not counted in total values) as it includes all values, including text. AVERAGEA function interprets text values as zero and it considers it as a valid number.

Syntax

Where the arguments (num1, num2, num3..) can be a value, range or even a cell reference.

Steps to Calculate the Average using AVERAGEA function

  1. Enter the data in your Excel worksheet. In the below, as you can see we have numeric data in B2, B3, B4, B5, and B6 cells whereas B3 contains Text value "Fail".
    How to calculate average in Excel
  2. Next, to find the Average go to the B7 cell and type the Average formula. Start the formula with equal to (=) followed with AVERAGEA. In the arguments, we will pass the cell reference, take your cursor to the B2 cell, and drag the mouse till B6. It will select all the cells and point to the range of cells, i.e., = AVERAGEA (B2:B6).
    How to calculate average in Excel
  3. Press the Enter button on your keyboard to get the average of the selected number. You will have the following output:
    How to calculate average in Excel
  4. Let's compare the output of AVERAGEA with the output of the AVERAGE function. You will notice in AVERAGEA, the sum is divided by 5 (the text field is also calculated as a valid value), whereas with the AVERAGE function, the sum is divided by 4 (it ignores the text value). You will have the following outputs:
    How to calculate average in Excel

3. Excel AVERAGEIF Function

The AVERAGEIF function in excel allows the user to apply the average that satisfies particular criteria. With this function, Excel looks within the specified range for a given condition and then determines the arithmetic mean of the values that match that condition.

Syntax

Parameters

  • Range (required) - This parameter specifies the location or range of the cells that meet the criteria.
  • Criteria (required) - The criteria parameter represents the value or expression that Excel will match within the specified range.
  • Average_range (optional) - It specifies the range of cells where the values to be averaged are placed. If the user doesn't specify this parameter, the range is used.

Steps to Calculate the Average using AVERAGEA function

1. Enter the data in your Excel worksheet. In the below, as you can see we have data in B2, B3, B4, B5 and B6 cells.

How to calculate average in Excel

2. Next, to find the Average go to the B7 cell and type the AVERAGEIF formula. Start the formula with equal to (=) followed with AVERAGEIF. In the arguments, we will pass the range and the criteria. In the criteria, we have passed a logical expression ">40". Therefore it will only select the cells that have a value greater than 40.

3. The above syntax will find the average of the cell values in the range B2 to B6 greater than 40. We have not entered any value in the third parameter; therefore, the cells specified in the range parameter are used to calculate the average of the values greater than 40.

How to calculate average in Excel

4. Press the Enter button on your keyboard to get the average of the selected number. You will have the following output:

How to calculate average in Excel





Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA