As an occasional Excel user or a statistics guru, one must know how to find Mean in Excel. While it may not seem like anything more than simply adding and dividing values to someone new in the Excel arena, you know that there is more to it than this. And that is precisely what has brought you here!

This visual guide on how to find Mean in Excel is designed and written in a specific way to ensure easy and effective learning. It will add a hundred more experience points to the tally and simplify your life! We can say this one thing with full confidence before we even begin.

In this article, we discuss 8 perfect ways for finding Mean values in Excel. If you can master all eight methods, there is nothing that can hold you back when you are up for calculating mean values.

Let’s begin then!

Contents

- 8 Great Techniques for Finding Mean in Excel
- Method 1: Manual Calculation
- Method 2: With SUM & COUNT Functions
- Method 3: Finding Mean by Using the AVERAGE Function
- Method 4: With AVERAGEA Function
- Method 5: Using the AVERAGEIF Function
- Method 6: Utilizing the AVERAGEIFS Function
- Method 7: Using the MEDIAN Function
- Method 8: The Status Bar Magic
- Final Thoughts

### How to Find Mean in Excel: Understanding Mean and Average

Understanding what Mean and Average are is necessary before we dive into the methods. Usually, these two terms are used interchangeably. However, there are some distinctions that no one should avoid; especially if you are to use Excel to find Mean values. Not being aware of them may confuse you because we will also use various *AVERAGE *functions to find the *Mean *in this guide.

Now, what are the Average and Mean? The term Average refers to the process of adding up all the values in a given dataset and then dividing the total value by the number of values from that dataset. Mean also does the same thing. It adds all the values and then divides them by the number of values. So, where is the difference, you ask?

As we learned in Mathematics, the term Average gives the central point of given values, and it is a mathematical term. In Statistics, “Average” is termed as “Mean”. To be more specific, what the mathematical term refers to as Average is known as Arithmetic Mean in Statistics. And using the term “Mean” without mentioning anything specific points to this Arithmetic Mean (AM).

There is more than one type of Mean. For instance, there is the Geometric Mean (GM) and Harmonic Mean (HM) as well. Hence, it could be said that while all the Averages are Mean, not all the Means are Average. So, when we say we are going to show how to find Mean in Excel, we are showing how to find the statistical Arithmetic Mean that is simply known as Mean.

In other words, we are showing how to find the mathematical Average. In this case, these terms are completely interchangeable.

## 8 Great Techniques for Finding Mean in Excel

Now that you know what Mean and Average are, it’s time to explore 8 excellent methods on how to find Mean in Excel. We are going to discuss all these methods with proper examples and practical applications. If you follow the instructions step by step, you will learn all the methods in no time!

We believe in the practice of learning through application. We are sharing the Excel file with you that contains all the datasets we are using for this guide. All the pictures in this article match this Excel sheet. Using this worksheet, you can follow the steps or practice in your own time. It’s your choice.

Now let’s start with our first method!

### Method 1: Manual Calculation

In terms of finding mean values, manual calculation has been the way since its inception. You take the values and add them up to calculate the total value and divide them by the number of values. This is how you know how to do it since you were in school, right?

Let’s see how you can do the same in Excel. This is the dataset we are going to work with in this method. We will use this dataset for the first four methods and then change some of the data according to the requirements.

**1.** Click on the cell where you want to calculate and find the mean.

**2.** Next, input = and then start adding up the values of cells. In our case, they were in column D as you can see in the picture. Make sure to put them within brackets before dividing them with the count of values. Otherwise, the divisor 10 in this example will take only the last number as the dividend instead of counting the whole. Finally, hit **Enter** to see the mean value of those numbers.

**3.** You will see that the mean value is now calculated and displayed in your selected cell.

This method may seem too obvious. But sometimes, if you do not have your numbers in an Excel sheet and have to calculate them by adding up a chain of values, this method is the one for that kind of occasion. Especially if you have to calculate a separate list of values and then even crosscheck those values, this method is there to serve you.

So, depending on the situation, the oldest method may still offer the best possible solution. Don’t count them out just yet!

### Method 2: With SUM & COUNT Functions

Finding the mean in Excel can also be done by using two common functions: the *SUM* function and the *COUNT* function. And we are going to use the Cell Reference technique for this method.

To explain how it works, the *SUM* function simply adds all the values in a selected range of cells. Meanwhile, the *COUNT *function goes through the selected range and counts the number of cells that have values in them. Therefore, when the mean value is to be found, *SUM* is the dividend and *COUNT* is the divisor.

Unlike the previous method where manual calculation took place, here *SUM* is doing all the additions as you point out the range and the *COUNT* function is calculating the number of values. So, this is a faster and quicker method than **Method 1**.

Let’s get down to business and see how it is done!

**1.** Click on the cell where you want to apply *SUM/COUNT* formulas.

**2.** Now, start writing the formula with = (equal sign) and then write SUM( . Next, select the range of cells with values that you want to find the mean of. In our case, it was from cells **D2** through **D11**. After selecting the range, close the parentheses and put / (forward slash) as the arithmetic operator for division.

**3.** Now, after the forward slash (/), write COUNT( and select the cell range. It should be the same cell range as you put in the SUM part of the formula. In our case, it was from **D2** through **D11**. When done, press the **Enter** button. The entire formula should look like the one marked in the picture below.

**4.** As soon as you hit **Enter**, you will see the mean value of your selected values.

This method may seem too much work after you go through the next two methods. However, the situation may not always be like the one here when you have to select the values (*SUM*) and the number of values (*COUNT)* in the same range of cells.

To find the mean value based on a special requirement, you may have to select the values for *SUM* in one cell range and divide the total value with a *COUNT* from a different range of cells. This is where this method comes in handy.

Pretty straightforward, isn’t it?

### Method 3: Finding Mean by Using the AVERAGE Function

*AVERAGE* is one of the most used Statistical Functions in Excel. The term by default refers to Arithmetic Mean. Averages are arithmetic means, but not all the means are *AVERAGE. *We have already discussed it in the introduction.

Now, what has been done in **Method 1** and **Method 2**, the same thing can be done with this one function called *AVERAGE*. You just need to select the cell range and it will add up all the values in that range and then divide them by the number of values from that very range of selection, all by itself!

Let’s see how to do it in three simple steps.

**1.** Select the cell for calculating your mean or average of a cell range.

**2.** Now, start writing the formula with = (equal sign) followed by AVERAGE(. Now, select the cell range. In our case for this method, the range was from **D2** through **D11**.

**3.** Close the bracket to complete writing the formula. Now hit **Enter** to see the mean value of your selected cell range for values.

**4.** Now you can see the mean value for your selected range.

More straightforward than the previous two methods!

However, as opposed to what was discussed in **Method 2**, this one here with the *AVERAGE *function is only applicable when you are trying to calculate the mean value from the same cell range. Meaning, the values, and the count are all in the same cell range. Unlike the *SUM/COUNT* technique, it is not possible to manually point out where to take your number of values or divisors from in this method.

### Method 4: With AVERAGEA Function

Things are going to get more interesting from this method. When your cell range includes some texts besides numeric values, calculating the absolute mean value takes a slightly different route from just using **Method 3**’s *AVERAGE* function.

Why? Because the *AVERAGE* function considers only numeric values for calculation. It takes texts as “0” (Zero) numeric value and completely ignores it in the calculation. For this reason, we are going to use another statistical function in Excel, namely *AVERAGEA* to determine the absolute mean value.

Not only that, but we will also show you the difference between a simple *AVERAGE* function and this method’s *AVERAGEA* function for an effective understanding. First, take a look at the picture below. We have slightly modified the dataset for this method.

Notice that there are three cells with texts instead of numeric values in the D column. We are about to show you what impact they might have in finding out the absolute mean values.

Although the *AVERAGEA* function is the focus of this method, we have also kept the ** AVERAGE** cell to show you the result you will get with the

*AVERAGE*function in this case.

This way, it will be easier to understand how the *AVERAGEA* function differs from that other statistical function *AVERAGE* for finding the absolute mean value when your cell range has other texts instead of numeric values. Let’s start with the AVERAGE function and see what it does.

**1.** Write the *AVERAGE* formula and select the cell range, and press the **Enter** button.

**2.** You’ll see the mean value now. However, there is the problem of not counting the cells with texts in the calculation.

The mean value shown here only takes those cells with numeric values into consideration and counts those numbers of values. Here, the sum of 1227 is divided by 7 which brings up the mean value of 175.29. But the absolute mean value should be found instead by dividing with all the number of cells. *NO SALE* days should be considered too when finding the average quantity of sales for each day.

**3.** Now, to find the actual mean value that considers everything, write the *AVERAGEA* formula in your selected cell. Select the range of cells, close the bracket, and hit the **Enter** button to see the magic.

**4.** Now you will see the result.

Notice the difference between the final mean values shown between ** AVERAGE** and

**. Significantly different, isn’t it?**

*AVERAGEA*### Method 5: Using the AVERAGEIF Function

The *AVERAGEIF* function is yet another statistical function that helps find the mean value from a set of values. If you are to find the mean value of specific values against a criterion, this method will be your friend.

For this method, we are going to work with the dataset shown below.

Before we get into the formula and calculation, let’s take a look at how this function works. As already said, this works with the criterion that you include in the formula. As an example of this method, we are going to find the mean of only Beef sales from the dataset. Now, have a look at the picture below to understand the different elements of the formula.

The formula is =AVERAGEIF(range,criteria,[average_range]) . Let’s break it down. In our case, we are going to find the mean value of the amount we have got from selling “Beef”.

Here,

*AVERAGEIF*: The function.

*RANGE*: The range of cells from where the criterion will be picked up.*CRITERIA*: String of text that you can either write manually within quotations, or you can put the criterion in a cell and refer to it while writing your formula (as shown in the picture below).

We will show you both ways.

*AVERAGE_RANGE*: The range of cells from where values will be picked up to calculate the mean values against the criterion you have specified.

Now, let’s find out how to do it.

**1.** Start writing the formula with =AVERAGEIF( . Select the cell range and put a comma. Next, either write the criterion within quotations or do a Cell Reference (of where you have written the criteria beforehand).

**2.** After you have finished writing the formula, press the **Enter** button. In the example here, **C2:C11 **is the range of cells where the criterion is, **F10** is the cell that contains the criterion that will be looked up in the cell range **C2:C11**, and **D2:D11** is the range of cells from where values will be picked up against the defined criterion for calculating the mean value.

**3.** As you hit **Enter**, you will see the mean value for the declared criterion. In our example for this method, the defined criterion was “Beef” from the Cell Reference.

**4.** You can simply change the criterion in the cell that you are referencing as your criterion, and you will be shown the mean value for that instantly. For instance, we have changed the criterion to Salmon and got shown the mean value for the amount of Salmon sold.

**5.** As mentioned earlier, you don’t necessarily have to put the criterion in a cell. Rather, you can write it within your formula and see the result accordingly. Examine the picture below where we have put “Shrimp” as the criterion in the formula directly.

**6.** As always, hit the **Enter** button and see the result.

It is easier than it looks. Practice a few times and you’ll get a hold of it for the rest of your life!

### Method 6: Utilizing the AVERAGEIFS Function

What if you are to use more than one criterion for your mean value calculation? Wonder no more, say hello to the *AVERAGEIFS *function!

This statistical function helps you find values based on multiple criteria. Now, it is rather difficult to explain how it works using only words. As it is known, learning something through examples is the best way. So first, take a look at the modified dataset below.

Let’s say you want to find the mean value for the total amount of *Beef* that has been sold using the *Doorstep* delivery method. “Beef” and “Doorstep” are your two criteria for this demonstration then. You can put the criteria in cells and refer to them in the formula. Also, you can write them within the formula if you wish to.

The formula for the *AVERAGEIFS* functions is =AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,… .

Let’s break it down as we did in the previous method. Here,

*AVERAGEIFS*: The function.

*AVERAGE_RANGE*: The cell range for picking up the values for mean calculation. In this example, it is column E, cell range**E2:E11**which has the values.*CRITERIA_RANGE1*: The range of cells where the first criterion can be found. In this example, it is column C where the cell range of**C2:C11**has the first criterion somewhere.*CRITERIA1*: The first criterion. In this example, we are using “Beef” with the Cell Reference technique.*CRITERIA_RANGE2*: The range of cells that accommodates the second criterion. In this example dataset, the range is**D2:D11**in column D.*CRITERIA2*: The second criterion which will be looked for in the*CRITERIA_RANGE2*. In this example, we are going to take “Doorstep” as the second criterion using the Cell Reference technique.

Now, let’s see how it works in steps.

**1.** Write the formula in the cell you want to find the mean value. Follow the formula structure and select all the required range of cells. As you can see in the picture below, we are pointing to “Beef” as criteria 1 (cell **G8)** and “Doorstep” as criteria 2 (cell **G10)** as Cell References.

Press the **Enter **button when you have finished writing the formula.

**2.** Now you will see that only the values that met both the criteria have been taken and the mean value of them is shown.

Only two values meet the criteria of “Beef” and “Doorstep”, and they have been calculated according to the formula. The value for “Beef” that has a different criteria 2 (“Shop”, in this case) has been left out in the calculation.

**3.** You can simply change one or both criteria and the result will update itself. For instance, we have changed criteria 2 to “Shop”, and the updated result has been shown instantaneously!

It’s fun, isn’t it?

** Important Tip:** When you write the formula of

*AVERAGEIF*and

*AVERAGEIFS*, take a second look if you are following the format properly. In the

*AVERAGEIF*formula

*,*the

*CRITERIA_RANGE*is defined first, then comes the

*CRITERIA*, followed by the

*AVERAGE_RANGE*.

Contrarily, in the *AVERAGEIFS* formula, the *AVERAGE_RANGE *is defined first, then comes the *CRITERIA_RANGE1*, followed by the *CRITERIA1*, then *CRITERIA_RANGE2*, *CRITERIA2*, and so on.

### Method 7: Using the MEDIAN Function

The MEDIAN function may seem out of place at first in a guide where finding the mean value is the goal. But, as a fundamental part of statistical functions in Excel, the MEDIAN comes into play pretty strongly.

Let us go a bit deeper and find out how the trio of statistical functions MEAN, MEDIAN, and MODE are a must-know in calculating mean values.

MEAN, MEDIAN, and MODE are parts of *Descriptive Statistics. *They find the single value for a dataset that represents the middle point of this dataset’s distribution. As it is known in Statistics, MEAN, MEDIAN, and MODE measure given data and find out the *Central Tendency.*

Let’s start with understanding the basic idea of MEAN, MEDIAN, and MODE.

- MEAN: The arithmetic mean. The process of adding up multiple numeric values and dividing the sum of those values by the total number of values. By default, it is known as AVERAGE as well. However, while all the averages are mean, not all the means are average.

- MEDIAN: The value that represents the middle number in a distributed dataset. The data is a list of numeric values and is sorted either in ascending or descending order first. Then MEDIAN finds the middle number in that list. So, half the values in that list have a greater number than the MEDIAN while the other half is lower than the MEDIAN.

- MODE: In a given dataset with numeric values, MODE finds the most recurring value in the list.

In terms of what kind of data you are using, MEAN value (Average, in Mathematics) can be highly misleading while MEDIAN and MODE may offer a proper analysis.

Confused? Let’s just work with an example dataset and see how MEAN value can mislead but MEDIAN and MODE offer the most accurate data! For this method, we are working with a new dataset. See the picture below. The dataset contains experience ratings given out by the customers on a scale of 1 to 10. They may indicate the customer satisfaction level through a MEAN calculation. Or, will it?

As you can see, we are going to show you both MEAN and MEDIAN to give you an idea of when you should use MEAN and when you should use MEDIAN. You will understand why sometimes MEDIAN is what you are looking for to find the “contextual mean” if you will.

**1.** Calculate the mean by using the *AVERAGE *function as previously shown in **Method 3**.

**2.** You will see the MEAN value of those 13 numeric values from the cell range **C2:C14**. Now, let’s find out the MEDIAN mean by using the *MEDIAN *function as shown in the picture below.

**3.** As soon as you hit **Enter** after writing the formula, you will see the result for your MEDIAN calculation of those customer satisfaction ratings.

**4.** Notice how big the gap is between MEAN and MEDIAN? Now, on a rating scale of ten, 5.62 is not a bad mean value. It may tell you that you are not doing too badly with customer satisfaction. But when you look at the MEDIAN value, the coin flips! Let’s understand why it happened. We are taking those rating values to a different column and sorting them from lowest to highest.

See that out of those thirteen customers, only six customers had a satisfactory experience (6+ rating out of 10). They provided a rating of 8 or more. But the other seven customers had bad experiences and provided ratings of 3 or less. When the gap between higher and lower values is significantly big, MEDIAN provides a more accurate analysis as a part of *Descriptive Statistics*.

Now if you want to find out the most recurring rating that your customers are giving you, there is the *MODE* function that will provide the ultimate solution.

**5.** To find the MODE, select the cell and write the formula as shown below. Hit **Enter** to finish the process.

**6.** Now you will see that the MODE has found the most recurring rating point of 3 which is similar to the calculated MEDIAN value.

While MEAN alone gave you the average, it did not represent the actual scenario. By using MEDIAN and MODE together, the real situation has been described and necessary actions can now be taken to initiate improvement.

How amazing is that?

### Method 8: The Status Bar Magic

This one actually cannot be called a “method”. Still, as a technique, this is a great one if you want to have a quick look and avoid doing any calculations.

Excel’s status bar is a powerful place. And this is what you are going to use. The status bar is marked in the picture below.

Follow the step-by-step process to learn how you can get the mean value of a column along with other related information in the blink of an eye!

**1.** The first step is to enable some options in the status bar in case they are not already enabled. Right-click on your mouse in the status bar and from the opened list, make sure that the Average option is checked.

You can enable other options that are shown in the list too. Simply click on the items if they are not checked and those too will be shown in the status bar.

**2.** Now, select the column with values of which you want to find the mean value. You can select cells instead of selecting a whole column if needed. In our case, we are going to select column D and find out the mean value of those numeric values in that column.

**3.** As soon as you have selected the column, look in the status bar for the mean value. The average is the mean value which matches the manual calculation from cell **B12**.

If you have other options enabled, you can see the values for those options as well. In this case, besides the Average, we have the Count, Numerical Count, Minimum, Maximum, and Sum options for values enabled.

This is a quick and easy way that comes in handy for finding out not only Mean/Average values but also other values like Min/Max values, or the Sum even.

** Important Tip: **This technique uses the process shown in

**Method 3**for calculating mean values minus the hard work of writing the formula. All the factors that apply to

**Method 3**also apply to this

**Method 8**. However, use this Status Bar technique with caution. It is not always accurate especially when the dataset is not cleaned-up.

### Final Thoughts

We have talked about 8 different techniques that you can use to find the mean value for your set of data. We included all the related information that you may need while doing the calculation. As always, not all methods are equal. As the last word, we now tell you all about them in brief. It will help you decide when to use which method.

Let’s begin with what is good for straightforward arithmetic mean or average calculation. **Method 1** is simply a manual calculation that you can do on a calculator. However, it is still useful if you are to work with only a few values that are not already in an Excel sheet. It will help with cross-checking the values as well.

**Method 2** uses two functions to do the same thing **Method 1** does. Only Cell Referencing is used when writing the formula. **Method 3 **and **Method 8 **are the quickest for you. While **Method 3 **requires manually putting the formula in, it is more accurate than **Method 8** which will go berserk and show wrong values if the dataset is not clean.

And **Method 4 **through **Method 7** are for specific requirements. Nonetheless, we highly recommend that you learn them all so that there will be no obstacle for you when you are battling to find the mean values of a dataset. Practice these methods multiple times to learn effectively. We strongly suggest you use the Excel file we are sharing with you for this purpose.

Once you make these methods your friend, there is simply no stopping you. Practice, practice, and learn!

**Similar Post:**