Excel has made calculating the average of multiple cells a very easy task - just use the function AVERAGE(AVERAGE). But what if some values carry more weight than others? For example, in many courses, tests carry more weight than assignments. For such cases, it is necessary to calculate weighted average.
Excel does not have a function for calculating the weighted average, but there is a function that will do most of the work for you: SUMPRODUCT(SUM PRODUCT). And even if you've never used this feature before, by the end of this article you'll be using it like a pro. The method we use works in any version of Excel as well as other spreadsheets such as Google Sheets.
We prepare the table
If you are going to calculate a weighted average, you will need at least two columns. The first column (column B in our example) contains the scores for each assignment or test. The second column (column C) contains the weights. More weight means more influence of the task or test on the final grade.
To understand what weight is, you can think of it as a percentage of your final grade. In fact, this is not the case, since in this case the weights should add up to 100%. The formula that we will analyze in this lesson will calculate everything correctly and does not depend on the amount that the weights add up to.
We enter the formula
Now that our table is ready, we add the formula to the cell B10(any empty cell will do). As with any other formula in Excel, we start with an equal sign (=).
The first part of our formula is the function SUMPRODUCT(SUM PRODUCT). Arguments must be enclosed in brackets, so we open them:
SUMPRODUCT(
=SUMPRODUCT(
Next, add the function arguments. SUMPRODUCT(SUMPRODUCT) can have multiple arguments, but usually two are used. In our example, the first argument will be a range of cells. B2:B9 A that contains the scores.
SUMPRODUCT(B2:B9
=SUMPRODUCT(B2:B9
The second argument will be a range of cells C2:C9, which contains the weights. These arguments must be separated by a semicolon (comma). When everything is ready, close the brackets:
SUMPRODUCT(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Now let's add the second part of our formula, which will divide the result calculated by the function SUMPRODUCT(SUMPRODUCT) by the sum of the weights. We will discuss later why this is important.
To perform the division operation, we continue the already entered formula with the symbol / (straight slash), and then write the function SUM(SUM):
SUMPRODUCT(B2:B9;C2:C9)/SUM(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
For function SUM(SUM) we will specify only one argument - a range of cells C2:C9. Don't forget to close the parentheses after entering the argument:
SUMPRODUCT(B2:B9;C2:C9)/SUM(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Ready! After pressing the key Enter, Excel will calculate the weighted average. In our example, the final result will be 83,6 .
How it works
Let's break down each part of the formula, starting with the function SUMPRODUCT(SUMPRODUCT) to understand how it works. Function SUMPRODUCT(SUMPRODUCT) calculates the product of each item's score and its weight, and then sums all the resulting products. In other words, the function finds the sum of the products, hence the name. So for Tasks 1 multiply 85 by 5, and for Testa multiply 83 by 25.
If you're wondering why we need to multiply the values in the first part, imagine that the higher the weight of the item, the more times we have to consider the grade for it. For example, Task 2 counted 5 times and Final exam- 45 times. That's why Final exam has a greater impact on the final grade.
For comparison, when calculating the usual arithmetic mean, each value is taken into account only once, that is, all values have equal weight.
If you could look under the hood of a function SUMPRODUCT(SUMPRODUCT), we saw that in fact she believes this:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Luckily, we don't need to write such a long formula because SUMPRODUCT(SUMPRODUCT) does all this automatically.
The function itself SUMPRODUCT(SUMPRODUCT) returns us a huge number − 10450 . At this point, the second part of the formula comes into play: /SUM(C2:C9) or /SUM(C2:C9), which returns the result to the normal range of scores, giving the answer 83,6 .
The second part of the formula is very important because allows you to automatically correct calculations. Remember that weights don't have to add up to 100%? All this thanks to the second part of the formula. For example, if we increase one or more weight values, the second part of the formula will simply divide by the larger value, again resulting in the correct answer. Or we can make the weights much smaller, for example by specifying values like 0,5 , 2,5 , 3 or 4,5 , and the formula will still work correctly. It's great, right?
The most common type of average is the arithmetic average.
simple arithmetic mean
The simple arithmetic mean is the average term, in determining which the total volume of a given attribute in the data is equally distributed among all units included in this population. Thus, the average annual output per worker is such a value of the volume of output that would fall on each employee if the entire volume of output was equally distributed among all employees of the organization. The arithmetic mean simple value is calculated by the formula:
Example 1 . A team of 6 workers receives 3 3.2 3.3 3.5 3.8 3.1 thousand rubles per month.simple arithmetic mean— Equal to the ratio of the sum of individual values of a feature to the number of features in the aggregate
Find the average salary
Solution: (3 + 3.2 + 3.3 +3.5 + 3.8 + 3.1) / 6 = 3.32 thousand rubles.
Arithmetic weighted average
If the volume of the data set is large and represents a distribution series, then a weighted arithmetic mean is calculated. This is how the weighted average price per unit of production is determined: the total cost of production (the sum of the products of its quantity and the price of a unit of production) is divided by the total quantity of production.
We represent this in the form of the following formula:
Example 2 . Find the average wages of shop workers per monthWeighted arithmetic mean- is equal to the ratio (the sum of the products of the attribute value to the frequency of repetition of this attribute) to (the sum of the frequencies of all attributes). It is used when the variants of the studied population occur an unequal number of times.
The average wage can be obtained by dividing the total wage by the total number of workers:
Answer: 3.35 thousand rubles.
Arithmetic mean for an interval series
When calculating the arithmetic mean for an interval variation series, the average for each interval is first determined as the half-sum of the upper and lower limits, and then the average of the entire series. In the case of open intervals, the value of the lower or upper interval is determined by the value of the intervals adjacent to them.
Averages calculated from interval series are approximate.
Example 3. Determine the average age of students in the evening department.
Averages calculated from interval series are approximate. The degree of their approximation depends on the extent to which the actual distribution of population units within the interval approaches uniform.
When calculating averages, not only absolute, but also relative values (frequency) can be used as weights:
The arithmetic mean has a number of properties that more fully reveal its essence and simplify the calculation:
1. The product of the average and the sum of the frequencies is always equal to the sum of the products of the variant and the frequencies, i.e.
2. The arithmetic mean of the sum of the varying values is equal to the sum of the arithmetic means of these values:
3. The algebraic sum of the deviations of the individual values of the attribute from the average is zero:
4. The sum of the squared deviations of the options from the mean is less than the sum of the squared deviations from any other arbitrary value, i.e.
When working with numerical expressions, sometimes there is a need to calculate their average value. called the arithmetic mean. In Excel, a spreadsheet editor from Microsoft, it is possible not to manually calculate it, but to use special tools. In this article, methods will be presented that allow you to find out and display the arithmetic mean.
Method 1: standard
First of all, let's analyze the method of how to calculate the arithmetic mean in Excel, which involves using a standard tool for this. The method is the simplest and most convenient to use, but it also has some disadvantages. But about them later, but now let's move on to the task.
- Select the cells in the column or row that contain the numeric values to be calculated.
- Go to the "Home" tab.
- On the toolbar in the "Editing" category, click on the "AutoSum" button, but you need to click on the arrow next to it so that a drop-down list appears.
- In it you need to click on the item "Average".
As soon as you do this, the result of the calculation of the arithmetic mean of the selected values will appear in the cell next to it. Its location will depend on the data block, if you have selected a row, then the result will be to the right of the selection, if the column is below.
But as mentioned earlier, this method has its drawbacks. So, you will not be able to calculate the value from a range of cells, or cells located in different places. For example, if your table has two columns with numerical values adjacent to them, then by selecting them and performing the above steps, you will get the result for each column separately.
Method 2: Using the Function Wizard
There are many ways to find the arithmetic mean in Excel, and it is natural that with their help it is possible to bypass the limitations that the previous method implies. We will now talk about performing calculations by using the Function Wizard. So here's what you need to do.
- By clicking the left mouse button, select the cell in which you want to see the result of the calculation.
- Open the Function Wizard window by clicking on the "Insert Function" button located to the left of the formula bar or by using the Shift+F3 hotkeys.
- In the window that appears, find the line "AVERAGE" in the list, select it and click the "OK" button.
- A new window will appear for entering function arguments. In it you will see two fields: "Number1" and "Number2".
- In the first field, enter the addresses of the cells in which the numerical values for the calculation are located. This can be done both manually and with the help of a special tool. In the second case, click on the button located on the right side of the input field. The Wizard window will collapse and you will need to select the cells for calculation with the mouse.
- If another range of cells with data is located elsewhere on the sheet, then specify it in the "Number2" field.
- Do the data entry until you have entered all the necessary ones.
- Click the OK button.
Upon completion of the input, the Wizard window will close, and the result of the calculation will appear in the cell that you selected at the very beginning. Now you know the second way how to calculate the arithmetic mean in Excel. But not the last, so we move on.
Method 3: Through the Formula Bar
This method, how to calculate the arithmetic mean in Excel, is not much different from the previous one, but in some cases it may seem more convenient, so it’s worth sorting it out. For the most part, this method only offers an alternative way to invoke the Function Wizard.
As soon as all the actions of the list are completed, the Function Wizard window will appear in front of you, where you need to enter the arguments. You already know how to do this from the previous method, all subsequent actions are no different.
Method 4: manually entering a function
If you wish, you can avoid interacting with the Function Wizard if you know the arithmetic mean formula in Excel. In some situations, entering it manually will speed up the calculation process many times over.
To understand all the nuances, you need to look at the syntax of the formula, it looks like this:
AVERAGE(cell_address(number), cell_address(number))
It follows from the syntax that in the function arguments it is necessary to prescribe either the address of the range of cells in which the numbers to be counted are located, or the numbers themselves to be calculated directly. In practice, the use of this method is as follows:
AVERAGE(C4:D6;C8:D9)
Method 5: calculation by condition
- select the cell in which the calculation will be performed;
- click the "insert function" button;
- in the wizard window that appears, in the list, select the line "when";
- click OK.
After that, a window for entering function arguments will appear. It is very similar to what was demonstrated earlier, only now there is an additional field - "Condition". It is in it that the condition must be entered. So, by entering "> 1500", only those values \u200b\u200bthat are greater than the specified will be taken into account.
17.02.2017
Excel is a spreadsheet. It can be used to create a variety of reports. In this program, it is very convenient to perform various calculations. Many do not use even half of Excel's capabilities.
You may need to find the average value of numbers at school, as well as during work. The classic way to determine the arithmetic mean without using programs is to add all the numbers, and then divide the resulting sum by the number of terms. If the numbers are large enough, or if the operation needs to be performed many times for reporting purposes, the calculations can take a long time. This is an irrational waste of time and effort, it is much better to use the capabilities of Excel.
Finding the arithmetic mean
Many data are already initially recorded in Excel, but if this does not happen, it is necessary to transfer the data to a table. Each number for the calculation must be in a separate cell.
Method 1: Calculate the average value through the "Function Wizard"
In this method, you need to write a formula for calculating the arithmetic mean and apply it to the specified cells.
The main inconvenience of this method is that you have to manually enter cells for each term. If there are a lot of numbers, this is not very convenient.
Method 2: Automatic calculation of the result in selected cells
In this method, the calculation of the arithmetic mean is carried out in just a couple of mouse clicks. Very handy for any number of numbers.
The disadvantage of this method is the calculation of the average value only for numbers located nearby. If the necessary terms are scattered, then they cannot be selected for calculation. It is not even possible to select two columns, in which case the results will be presented separately for each of them.
Method 3: Using the formula bar
Another way to go to the function window:
The fastest way, in which you do not need to search for a long time in the menu, you need items.
Method 4: Manual Entry
It is not necessary to use the tools in the Excel menu to calculate the average value, you can manually write the necessary function.
A quick and convenient way for those who prefer to create formulas with their own hands, rather than looking for ready-made programs in the menu.
Thanks to these features, it is very easy to calculate the average value of any numbers, regardless of their number, and you can also compile statistics without manually calculating them. With the help of the tools of the Excel program, any calculations are much easier to make than in the mind or using a calculator.