Как посчитать mean в excel
There are many functions in order to find the average in Excel (although it does not matter what kind of value it is: numerical, textual, percentage or other). And each of them has its own peculiarities and advantages. After all, certain conditions can be put in this task.
For example, the average in Excel is counting using statistical functions. You can also manually enter your own formula. Consider the various options.
How to find the arithmetic mean?
It is necessary to add all the numbers in the set and divide the sum by the number in order to find the arithmetic mean. For example, the student's marks in computer science: 3, 4, 3, 5, 5. The average rating is 4 for a quarter. We found the arithmetic mean using the formula: =(3 + 4 + 3 + 5 + 5) / 5.
How can you quickly do this with Excel functions? Take for example a number of random numbers in a row:
- We put the cursor in cell A2 (under a set of numbers). In the menu – «HOME»-«Editing»-«AutoSum»-«Average» button. A formula appears after clicking in the active cell. Select the range: A1: H1 and press ENTER.
- The second method is based on the same principle of finding the arithmetic mean. But we will call the function AVERAGE differently. Using the function wizard (use fx button or key combination SHIFT + F3).
- The third way to call the AVERAGE function from the panel: «FORMULAS»-«More Function»-«Statistical»-«AVERAGE».
Or you can make cell to be active and just manually enter the formula: =AVERAGE(A1:A8).
Now let's see what the AVERAGE function be able to:
Let us find the arithmetic mean of the first two and three last numbers. Formula: =AVERAGE(A1:B1,F1:H1).
Average value by condition
A numerical criterion or a textual criterion can be the condition for finding the arithmetic mean. We will use the function: = AVERAGEIF().
Find the arithmetic mean for numbers that are greater or equal to 10.
The result of using the function "AVERAGEIF" by the condition ">=10" is next:
The third argument "Averaging Range" is omitted. Firstly, it is not necessary. Secondly, the range analyzed by the program contains ONLY numeric values. In the cells specified in the first argument, the search will be performed according to the condition specified in the second argument.
Attention! You can specify the search criteria in the cell. And in the formula make a reference to it.
Let's find the average value of numbers by the text criterion. For example, the average sales of goods "Tables".
The function will look like this:
Range is a column with the names of goods. Search criteria is a link to a cell with the word "Tables" (you can insert the word "Tables" instead of the A7 link). The averaging range is the cells from which data will be taken to calculate the arithmetic mean.
We get the following value as a result of calculating using the function:
Attention! It is mandatory to point the averaging range for the text criterion (condition).
How to calculate the weighted average price in Excel?
How to calculate the average percentage in Excel? For this purpose, the SUMPRODUCTS and SUM functions are suitable. Table for an example:
How did we know the weighted average price?
Using the formula =SUMPRODUCT() we learn the total revenue after the realization of the entire quantity of goods. And the =SUM() function sums the goods quantity. We found a weighted average price having divided the total revenue from the sale of goods by the total number of units of the goods. This indicator takes into account the "weight" of each price and its share in the total mass of values.
The standard deviation: the formula in Excel
There is a standard deviation in the entire population and in the sampling. In the first case, this is the square root of the general variance. In the second, this is a square root of the sampling variance.
A dispersion formula is compiled to calculate this statistical indicator. The square root is extracted from it. But in Excel, there is a ready-made function for finding the root-mean-square deviation.
The root-mean-square deviation is related to the scope of the initial data. This is not enough for a figurative representation of the variation of the analyzed range. The coefficient of variation is calculated to obtain the relative level of the data variability.
standard deviation / arithmetic mean
The formula in Excel is as follows:
STDEV.P (range of values) / AVERAGE (range of values).
The coefficient of variation is considered as a percentage. Therefore, set the percentage format in the cell set.
In one of the previous articles, we discussed three essential functions for calculating average in Excel, which are very straightforward and easy-to-use. But what if some of the values have more "weight" than others and consequently contribute more to the final average? In such situations, you'll need to calculate the weighted average.
Although Microsoft Excel doesn't provide a special weighted average function, it does have a couple of other functions that will prove useful in your calculations, as demonstrated in the formula examples that follow.
What is weighted average?
Weighted average is a kind of arithmetic mean in which some elements of the data set carry more importance than others. In other words, each value to be averaged is assigned a certain weight.
Students' grades are often calculated using a weighted average, as shown in the following screenshot. A usual average is easily calculated with the Excel AVERAGE function. However, we want the average formula to consider the weight of each activity listed in column C.
In mathematics and statistics, you calculate weighted average by multiplying each value in the set by its weight, then you add up the products and divide the products' sum by the sum of all weights.
In this example, in order to calculate the weighted average (overall grade), you multiply each grade by the corresponding percentage (converted to a decimal), add up the 5 products together, and divide that number by the sum of 5 weights:
As you see, a normal average grade (75.4) and weighted average (73.5) are different values.
Calculating weighted average in Excel
In Microsoft Excel, weighted average is calculated using the same approach but with far less effort because Excel functions will do most of the work for you.
Example 1. Calculating weighted average by using the SUM function
If you have basic knowledge of the Excel SUM function, the below formula will hardly require any explanation:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6,)/SUM(C2:C6)
In essence, it performs the same calculation as described above, except that you supply cell references instead of numbers.
As you can see in the screenshot, the formula returns exactly the same result as the calculation we did a moment ago. Notice the difference between the normal average returned by the AVERAGE function (C8) and weighted average (C9).
Although the SUM formula is very straightforward and easy to understand, it is not a viable option if you have a large number of elements to average. In this case, you'd better utilize the SUMPRODUCT function as demonstrated in the next example.
Example 2. Finding weighted average with SUMPRODUCT function
Excel's SUMPRODUCT function fits perfectly for this task since it is designed to sum products, which is exactly what we need. So, instead of multiplying each value by its weight individually, you supply two arrays in the SUMPRODUCT formula (in this context, an array is a continuous range of cells), and then divide the result by the sum of weights:
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)Supposing that the values to average are in cells B2:B6 and weights in cells C2:C6, our Sumproduct Weighted Average formula takes the following shape:
=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)
To see the actual values behind an array, select it in the formula bar and press the F9 key. The result will be similar to this:
So, what the SUMPRODUCT function does is multiply the 1 st value in array1 by the 1 st value in array2 (91*0.1 in this example), then multiply the 2 nd value in array1 by the 2 nd value in array2 (65*0.15 in this example), and so on. When all of the multiplications are done, the function adds up the products and returns that sum.
To make sure that the SUMPRODUCT function yields a correct result, compare it to the SUM formula from the previous example and you will see that the numbers are identical.
When using either the SUM or SUMPRODUCT function to find weight average in Excel, weights do not necessarily have to add up to 100%. Nor do they need to be expressed as percentages. For example, you can make up a priority / importance scale and assign a certain number of points to each item, as demonstrated in the following screenshot:
Well, that's all about calculating weighted average in Excel. You can download the Weighted Average spreadsheet and try the formulas on your data. In the next tutorial, we are going to have a close look at calculating moving average. I thank you for reading and look forward to seeing you next week!
The average function is used to calculate the Arithmetic Mean of the given input. It is used to do sum of all arguments and divide it by the count of arguments where the half set of the number will be smaller than the mean, and the remaining set will be greater than the mean. It will return the arithmetic mean of the number based on provided input. It is an in-built Statistical function. A user can give 255 input arguments in the function.
Excel functions, formula, charts, formatting creating excel dashboard & others
As an example, suppose there is 4 number 5,10,15,20 if a user wants to calculate the mean of the numbers then it will return 12.5 as the result of =AVERAGE (5, 10, 15, 20).
The formula of Mean: It is used to return the mean of the provided number where a half set of the number will be smaller than the number, and the remaining set will be greater than the mean.
The argument of the Function:
- number1: It is a mandatory argument in which functions will take to calculate the mean.
- [number2]: It is an optional argument in the function.
Examples on How to Find Mean in Excel
Here are some examples of how to find mean in excel with the steps and the calculation
You can download this How to Find Mean Excel Template here – How to Find Mean Excel TemplateLet’s assume there is a user who wants to perform the calculation for all numbers in Excel. Let’s see how we can do this with the average function.
Step 1: Open MS Excel from the start menu >> Go to Sheet1, where the user has kept the data.
Excel Training (20 Courses, 9+ Projects) 20 Online Courses | 9 Hands-on Projects | 105+ Hours | Verifiable Certificate of Completion | Lifetime Access4.9 (9,044 ratings)
Step 2: Now create headers for Mean where we will calculate the mean of the numbers.
Step 3: Now calculate the mean of the given number by average function>> use the equal sign to calculate >> Write in cell C2 and use average>> “=AVERAGE (“
Step 3: Now, it will ask for a number1, which is given in column A >> there are 2 methods to provide input either a user can give one by one or just give the range of data >> select data set from A2 to A11 >> write in cell C2 and use average>> “=AVERAGE (A2: A11) “
Step 4: Now press the enter key >> Mean will be calculated.
Summary of Example 1: As the user wants to perform the mean calculation for all numbers in MS Excel. Easley everything calculated in the above excel example, and the Mean is 27.5 for sales.
Step 1: Open MS Excel from the start menu >> Go to Sheet2, where the user has kept the data.
Step 2: Now create headers for Mean where we will calculate the mean of the numbers.
Step 3: Now calculate the mean of the given number by average function>> use the equal sign to calculate >> Write in cell D2 and use AVERAGEA>> “=AVERAGEA (“
Step 4: Now, it will ask for a number1, which is given in column B >> there is two open to provide input either a user can give one by one or just give the range of data >> select data set from B2 to B11 >> write in D2 Cell and use average>> “=AVERAGEA (D2: D11) “
Step 5: Now click on the enter button >> Mean will be calculated.
Step 6: Just to compare the AVERAGEA and AVERAGE, in normal average, it will exclude the count for text value so mean will high than the AVERAGE MEAN.
Summary of Example 2: As the user wants to perform the mean calculation for all number in MS Excel. Easley, everything calculated in the above excel example and the Mean is $146377.80 for sales.
Step 1: Open the MS Excel from the start menu >> Go to Sheet3, where the user kept the data.
Step 2: Now create headers for Mean where we will calculate the mean of the numbers.
Step 3: Now calculate the mean of the given number by average function>> use the equal sign to calculate >> Write in F2 Cell and use AVERAGE >> “=AVERAGE (“
Step 3: Now, it will ask for number1 which is given in B, C, and D column >> there is two open to provide input either a user can give one by one or just give the range of data >> Select data set from B2 to D2 >> Write in F2 Cell and use average >> “=AVERAGE (B2: D2) “
Step 4: Now click on the enter button >> Mean will be calculated.
Step 5: Now click on the F2 cell and drag and apply to another cell in the F column.
Summary of Example 3: As the user wants to perform the mean calculation for all number in MS Excel. Easley, everything calculated in the above excel example and the Mean is available in the F column.
Things to Remember About How to Find Mean in Excel
- Microsoft Excel’s AVERAGE function used to calculate the Arithmetic Mean of the given input. A user can give 255 input arguments in the function.
- Half the set of a number will be smaller than the mean, and the remaining set will be greater than the mean.
- If a user calculating the normal average, it will exclude the count for a text value, so AVERAGE Mean will bigger than the AVERAGE MEAN.
- Arguments can be number, name, range or cell references that should contain a number.
- If a user wants to calculate the mean with some condition, then use AVERAGEIF or AVERAGEIFS.
Recommended Articles
This is a guide to How to Find Mean in Excel. Here we discuss How to Find Mean along with examples and a downloadable excel template. You may also look at the following articles to learn more –
In statistic, the population is basically a collection of a group of things. This can be of numbers, people, objects, etc. So the population means is nothing but the average of this group of items. It is basically arithmetic mean of the group and can be calculated by taking a sum of all the data points and then dividing it by the number of items we have in the group. It is the most common method to measure the center of a data set but it is very rare that we calculate population mean. The reason for that is population is a big data set and it is very time-consuming and costly to find the population mean. For example, the age of people living in Washington DC is the population set; it is very difficult to count each person and then take an average. So usually what we do is that we extract a sample out of the population which is a representation of population set and we take an average of a sample of seeing what is the average of the population.
Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others
A formula for Population Mean is given by:
Population Mean = Sum of All the Items / Number of ItemsIn case you want to use the sample mean as representative of the population mean:
Sample Mean = Sum of All the Items in Sample / (Number of Items in Sample – 1)Examples of Population Mean Formula (With Excel Template)
Let’s take an example to understand the calculation of Population Mean formula in a better manner.
You can download this Population Mean Template here – Population Mean TemplateLet say you have a data set with 10 data points and we want to calculate Population Mean for that.
Data set :
Solution:
Population Mean is calculated using the formula given below
Population Mean = Sum of All the Items / Number of Items
Let say you want to invest in IBM and very keen to look at its past performance and returns. You want to go back 20 years and calculate monthly return but that will become very hectic. So you have decided to take a sample of the last 10 months and calculate return and mean of that. You believe that the sample you have taken is a correct representation of the population.
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) 250+ Online Courses | 1000+ Hours | Verifiable Certificates | Lifetime Access4.9 (3,296 ratings)
Solution:
So if you see here, in the last 10 months, IBM return has fluctuated very much.
Sample Mean is calculated using the formula given below
Sample Mean = Sum of All the Items in Sample / (Number of Items in Sample – 1)
Overall, in the last 10 months, the average return is only 0.92%.
Explanation
Relevance and Uses of Population Mean Formula
In general, Population Mean is very simple yet one of the crucial elements of statistics. It is the basic foundation of statistical analysis of data. It is very easy to calculate and easy to understand also. But as mentioned above, the population mean is very difficult to calculate, so it is more of a theoretical concept. It does not make sense to spend enormous efforts to find a mean of population set. So sample mean is a more realistic and practical concept. Also, mean value, if look it in a silo, has relatively less significance because of the flaws discussed above and it is more of a theoretical number. So we should use mean value very carefully and should not analyze the data only based on the mean.
Population Mean Formula Calculator
You can use the following Population Mean Calculator
Recommended Articles
This has been a guide to Population Mean Formula. Here we discuss how to calculate Population Mean along with practical examples. We also provide a Population Mean calculator with downloadable excel template. You may also look at the following articles to learn more –
Читайте также: