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!
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 AVERAGEA. Significantly different, isn’t it?
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”.
- 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.
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!