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.

**11 Suitable Methods to Calculate Average in Excel**

**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:

`=AVERAGE(D5:D15)`

**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:

`=SUM(D5:D15)/COUNTA(D5:D15)`

**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:

`=SUBTOTAL(1,D5:D15)`

**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:

`=SUMPRODUCT(C5:C12,D5:D12)`

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:

`=SUMPRODUCT(C5:C12,D5:D12)/SUM(D5:D12)`

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:

`=AVERAGEIF(B5:B15,D17,D5:D15)`

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:

`=AVERAGEIF(D5:D15,"<>"&0)`

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:

`=AVERAGEIFS(D5:D15,B5:B15,”California”,B5:B15,”Texas”)`

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:

`=SUM(SUMIF(B5:B15,D17:D18,D5:D15))/ SUM(COUNTIF(B5:B15,D17:D18))`

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:

`=AVERAGE(LARGE(D5:D15,{1,2,3}))`

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

`=AVERAGE(SMALL(D5:D15,{1,2,3}))`

**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:

`=IFERROR(AVERAGE(E5:E15),"No Data Found")`

**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:

`=AVERAGEA(D5:D15)`

**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:

`=MEDIAN(D5:D15)`

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:

`=MODE(D5:D15)`

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:

`=MODE.MULT(D5:D15)`

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:

`=MODE.SNGL(D5:D15)`

**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:

`=TRIMMEAN(D5:D15,0.2)`

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.