How to Calculate Average in Excel (Including All Criteria)

In Microsoft Excel, there are several ways to calculate an average of a range of data. In this article, we’ve tried to include all possible criteria related to the arithmetic mean. Let’s have a glance at the methods described below and you’ll find your required criteria with easy and simple illustrations.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.

How to Calculate Average in Excel (11 Suitable Methods)

1. Calculate Simple Arithmetic Average of a Group of Numbers

An average is a number expressing the central or typical value in a set of data. We can approach in 4 different ways to determine the simple arithmetic mean of a range of numeric vales.

1.1 Use Excel AutoSum to Find Average for a Group of Numbers Quickly

Let’s assume that we have a dataset with sales and profits of some random products in different states. We’ll use the AutoSum feature in our first method to find out the average sales.

📌 Step 1:

➤ First, select the output Cell D17.

➤ Now, go to the Formulas tab.

➤ From the AutoSum drop-down, select the Average command.

The AVERAGE function will be activated in the corresponding cell.

📌 Step 2:

➤ Select all the sales values from the Sales column.

📌 Step 3:

➤ Press Enter and you’ll find the average sales in the output Cell D17 at once.

1.2 Use of Basic AVERAGE Function to Find Out Average in Excel

You can also manually input the AVERAGE function to calculate the average in Excel. The required formula in Cell D17 will look like the following:

1.3 Determine Average of Numbers Manually in Excel

We can again combine the SUM and COUNTA functions to determine the average of sales. The SUM function simply sums up the values in a range and the COUNTA function counts all the non-empty cells in the selected range or an array. So, if we divide the sum of all sales by the number of instances counted by the COUNTA function, we’ll get the average sales.

The combined formula with the SUM and COUNTA functions will be:

1.4 Use of Excel SUBTOTAL Function to Find Out Average

The SUBTOTAL function returns a subtotal in a list or database. What we have to do here is simply select the function number of the AVERAGE parameter from the function list and then input the range of values in the second argument.

When you’ll activate the SUBTOTAL function, a list of functions with their reference numbers will open up first as shown in the picture below.

The final formula with the SUBTOTAL function to calculate the average sales in Cell D17 will be:

2. Calculate Weighted Average by Combining SUMPRODUCT and SUM Functions

A weighted average is the average of numbers that have varying degrees of importance in a dataset. For example, the following image represents a student’s marks sheet in a final exam. Each subject has a weightage percentage that we have to apply to calculate the average marks in Cell D14.

In this example, all the weightage factors or percentages add up to 100%. So we don’t need to divide the sum of all weightage factors while calculating the average marks. What we have to do here is multiply all marks with their corresponding weightage factors and sum all the products accordingly.

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. By using this function, we have to input the range of cells containing all marks in the first argument. The second argument will accommodate all the weightage factors. So, the required formula with the SUMPRODUCT function to find out the weighted average marks will be:

After pressing Enter, we’ll get the resultant value as shown in the following image.

But if the weightage percentages don’t add up to 100%, then we have to divide the return value obtained from the SUMPRODUCT function by the sum of all the weightage factors. And the final formula would look like the following:

Now press Enter and you’ll find the average marks considering all the weightage factors.

3. Count Average of Numbers with a Single Criterion

Now we’ll find the average of sales values based on a specific criterion or condition. The AVERAGEIF function is the best-suited application here to get the job done. The AVERAGEIF function finds the arithmetic mean for the cells specified by a given condition or criterion. Let’s say, we want to know the average sales of all the branches in California state only.

The required formula in the output Cell D18 will be:

After pressing Enter, we’ll get the following output right away.

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)

4. Determine the Average of Number Ignoring Zero (0) Values

To calculate the average of numeric values in a range while ignoring all zeros (0), we have to insert the defined criteria in the AVERAGEIF function. The criteria argument will use a mathematical operator that excludes all zero values. So, the required formula in the output Cell D17 will be:

After pressing Enter, the resultant value will be as displayed in the following screenshot.

Read More: How to Calculate Average in Excel Excluding 0 (2 Methods)

5. Calculate Average of Numbers with Multiple Criteria in Excel

The AVERAGEIFS function is able to accept multiple criteria. But this function is unable to take multiple OR criteria and it accepts only AND criteria from single or different columns or rows.

For example, we want to know the average sales in California and Texas. With the AVERAGEIFS function, the required formula should be as stated below:

Now press Enter and the function will return a #DIV/0! error. It means the function cannot return the output from a single column with multiple OR criteria.

The alternative formula to find out the average sales in California and Texas could be:

And now you’ll get the desired return value without encountering any error.

🔎 How Does the Formula Work?

• SUMIF(B5:B15,D17:D18,D5:D15): The SUMIF function in the numerator part of the division returns the total sales for California and Texas separately in an array. The return output is as follows:

{118133;77690}

• SUM(SUMIF(B5:B15,D17:D18,D5:D15)): The SUM function then simply adds up the total sales found in the preceding step and returns $1,95,823.00.

• COUNTIF(B5:B15,D17:D18): The COUNTIF function in the denominator counts all the cells containing ‘California’ and ‘Texas’ separately and thus it returns the output as:

{4;3}

• SUM(COUNTIF(B5:B15,D17:D18)): Now the SUM function sums the total counts found in the previous step and returns 7.
• SUM(SUMIF(B5:B15,D17:D18,D5:D15))/ SUM(COUNTIF(B5:B15,D17:D18)): Finally, the entire formula divides the total sales for California and Texas by the total counts and returns the output as $27,974.71.

Similar Readings

• How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
• How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
• Average Attendance Formula in Excel (5 Ways)
• How to Determine Triple Exponential Moving Average in Excel
• How to Calculate Centered Moving Average in Excel (2 Examples)

6. Calculate the Average of Top or Bottom 3 by Using LARGE or SMALL Functions in Excel

By using the LARGE function, we can, first of all, find the top 3 sales. And then the AVERAGE function will return the average of those 3 outputs.

The combined formula with the LARGE and AVERAGE functions in the output Cell D17 will be:

Similarly, by incorporating the AVERAGE and SMALL functions, we can determine the average of bottom 3 sales too. And the associated formula will be:

7. Ignore the #DIV/0! Error While Calculating Average in Excel

The #DIV/0! error returns when a function has to divide a numeric value by zero (0). While calculating the average, if the number of instances is absent or zero (0), the function will not return any valid output.

In this case, we can use the IFERROR function to convey a user-defined message if the function tends to return an error. As we see there is no data in the Profit column, so if we try to find the average of all cells present in this column, it’ll return a #DIV/0! error. By using the IFERROR function now, we’ll define a message “No Data Found” and this message will be the output when the formula has to deal with the return value with an error

The corresponding formula in the output Cell D17 will be now:

8. Insert Excel AVERAGEA Function to Include All Cells in a Range

The AVERAGEA function returns the arithmetic mean of all non-blank cells in a range. Arguments can be numbers, ranges, arrays, or references.

In the following dataset, there are 3 text values in the Sales column. If we use the AVERAGE function to evaluate the average, it will exclude the cells with text values and determine the average for all other values in the range.

But if we want to find out the average of all data available in the Sales column, then we have to use the AVERAGEA function. This function converts the text value in a cell to ‘0’ and thereby returns the average of all values present in a range.

The required formula with the AVERAGEA function here will be:

Note: The output with the AVERAGEA function must be less than or equal to the output obtained by the AVERAGE function for a similar range of cells. It’s because the AVERAGE function will look for the valid numeric values only and then return the arithmetic mean but the AVEARGEA function will consider all non-blank cells in a range.

9. Calculate Other Types of Average in Excel: Median and Mode

Apart from arithmetic mean, we can also determine the median and mode in a range of cells. The median is the middle number in a sorted, ascending or descending, list of numbers and can be more descriptive of that data set than the average. And the mode is the value that appears most frequently in a data set. A set of data may have one mode, more than one mode, or no mode at all.

In Microsoft Excel, there are MEDIAN and MODE functions available which can easily find out the desired parameter from a range of numeric values.

9.1 Use of MEDIAN Function

The median is usually different from the average of numbers in a range of cells. The average is the arithmetic mean of a set of numeric values. The median is not like that indeed. It finds out the middle number in a sorted list of numeric values. You don’t need to sort the range of data manually while finding the median with the MEDIAN function in Excel.

In the following dataset, the average sales value is $26,277.55. But the median here is $29,964.00. And you have to input the range of cells only containing the numbers in the MEDIAN function. The formula would look like this:

To get a more precise concept of how the MEDIAN function works, we can sort the Sales column in an ascending or descending order. There are 11 sales values in that column and the median lies in the middle or 6th row accordingly.

9.2 Use of MODE Function

The MODE function is compatible with all versions of Microsoft Excel but the latest ones don’t recommend applying this function. From the 2010 version, MODE.SNGL and MODE.MULT functions are being used to meet the requirements with more precision. We’ll find out how they work in the next two sections 9.3 and 9.4.

In the table below, the sales amount of $21,000.00 has appeared most (thrice) in the Sales column. So, the MODE function will return the mentioned sales amount after inserting all the sales references in the argument.

The required formula with the MODE function is as follows:

The MODE function is way too different from the outputs of AVERAGE and MEDIAN functions as this function will look for the highest occurrence of a number only in a dataset.

9.3 Use of MODE.MULT Function

A drawback of using the MODE function is if a dataset contains two different numbers with a similar occurrence, this function will return the number that is lying earlier in the data range. The following screenshot is an example of this problem where the function has returned $16,000.00 only although the sales value of $21,000.00 is also present with the same number of occurrences as $16,000.00.

And if you sort the Sales column in descending order, the MODE function will now return $21,000.00.

The MODE.MULT function is the ultimate solution to this drawback of the MODE function. The MODE.MULT function returns a vertical array of the most frequently occurring or repetitive values in an array or range of data.

The required formula with the MODE.MULT function is as follows:

And the function will return an array with the most and similar occurrences of the sales.

9.4 Use of MODE.SNGL Function

The MODE.SNGL function works as the MODE function. If you don’t want to see more than one output as the modes from a range of numbers, then you can opt to choose the MODE.SNGL function.

The required formula with this function is as follows:

10. Calculate Moving Average with Excel Analysis ToolPak

In statistics, a moving average is a calculation used to analyze data points by creating a series of averages of different subsets of the full data set.

For example, we have 5 numbers 10,20,25,35, and 50. If we take the interval as 2 to find out the moving averages from these numbers, then the output will be 15,22.5,30 and 42.5.

So how these moving averages have come out? The average of the 1st two numbers (10 and 20) is 15 and thereby the arithmetic mean of the 2nd and 3rd numbers (20 and 25) is 22.5. Thus we have taken two successive numbers starting from the initial point or value. And accordingly, the rest of the moving averages will come out by following a similar sequence.

Now let’s come to our dataset below. There are total sales lying by months in the following table. Based on these data, we’ll determine the moving average with a specific interval.

📌 Step 1:

➤ From the Data ribbon, select the Data Analysis command in the Analysis group.

📌 Step 2:

➤ In the Data Analysis window, select the Moving Average option and press OK. A dialog box will open up.

📌 Step 3:

➤ In the Input Range, select the entire column of total sales along with its header.

➤ Put a checkmark in the Label in the First Row option.

➤ Define a reference cell as the Output Range.

➤ If you want to see the moving average in a graphical chart, mark on the Chart Output option.

➤ Press OK and you’re done with the steps.

As we’re not inputting any interval, the default will be 3 here. It means the moving average will be counted for every 3 successive sales values from the beginning.

And the final outputs are as follows in the screenshot.

The following chart will show the data points of all total sales and the moving averages. If you click on any portion of the chart, it’ll indicate the reference columns in the data table.

11. Apply TRIMMEAN Function to Calculate Trimmed Mean in Excel

The TRIMMEAN function returns the mean of the interior position of a set of data values. The syntax of this function is:

=TRIMMEAN(array, percent)

Here, the array is the range of values of cell references, and the second argument, percent will define what portions have to be trimmed from the top and bottom values in a range.

In our following data table, the Sales column has 11 sales values. If we select the trim percentage as 20% or 0.2, the largest and the smallest sales values will be omitted while calculating the average of the rest of the data.

Let’s find out how this trim percentage works. We have 11 rows in the Sales column. So, 20% of 11 means 2.2. Now divide this 2.2 by 2 and the output is 1.1 that we can take approximately as 1. So, the largest 1 and smallest 1 sales values will not be counted during the calculation of the sales average.

The required formula with the TRIMMEAN function is as follows:

Now press Enter and you’ll get the average sales from a trimmed range of sales. Since some of the data are being cut away from the actual range, the output with the TRIMMEAN function will not be as same as the AVERAGE function.

We can also find the output obtained from the TRIMMEAN function by using the simple AVERAGE function. In that case, we have to calculate manually how many cells have to be opted out in a sorted data range while calculating the average.

Based on our dataset below, if we leave out the first and last sales values, the average sales will be $26,134.11 which is the same output as found by the TRIMMEAN function.

Concluding Words

I hope all of the methods mentioned in this article will now help you to apply them in your Excel spreadsheets when you need to calculate different types of averages from a range of data. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

• Running Average: How to Calculate Using Excel’s Average(…) Function
• How to Generate Moving Average in Excel Chart (4 Methods)
• Calculate Moving Average in Excel (4 Examples)
• How to Calculate Exponential Moving Average in Excel
• How to Calculate Percentage above Average in Excel (3 Easy Ways)