Calculate average weighted in Excel
Have you ever heard about the average weighted or average? Usually, we cannot calculate everything in our daily life. Let’s suppose calculate the weather of the week. In that case, we are not going to tell the everyday temperature. Instead of that, we will find an estimation of the week temperature.
Usually, users calculate the average as Sum of values/Number of values. It gets back the average of the values. To calculate the weighted average, you can use the SUM and SUMPRODUCT functions of Excel.
There could be various scenarios where you may need to calculate the average weighted. Hence, we will describe the ways to calculate the average weighted in MS Excel using its built-in formulas.
How to calculate average weighted?
The Excel users require two functions to calculate the average weighted, i.e., SUM and SUMPRODUCT. These functions will be used to find out the average weighted in Excel. If you simply use the AVERAGE function, it will find the normal average. So, remember – the simple average and average weighted both are different.
Now, let’s see how the SUM and SUMPRODUCT functions work to calculate average weighted.
SUM()
You might have already used the SUM() function earlier. It is a most used function of Excel that helps to calculate the sum of numbers. This function enables the users to calculate the sum for the range of cells.
Syntax
In Excel, use the function as below:
SUMPRODUCT()
The SUMPRODUCT() is another function used with SUM() function to calculate the average weighted. It returns the sum of products of the values given in an array or in the argument. This function is usually required when the user needs to multiple cells together.
Syntax
In Excel, use the function as below:
Usually, the SUMPRORDUCT() function takes two arguments. One for values array and another for their weight array. So, these functions work together to calculate the average weighted.
Example 1: Simple Average calculation
Average and average weighted terms are different. So, please do not get confused between them. If the user simply uses the AVERAGE() function, It will return the simple average after calculation for the given values. For simple average calculation, Excel users use mathematical formulas, i.e., Sum of values/Number of values.
Following are the examples and steps to calculate the simple average. Follow the given one:
Step 1: Suppose we have the following data of three subjects marks of a student. For which, we need to calculate the average.
Step 2: So, we will use the simple mathematic Excel formula. Select the targeted cells and navigate to the Formulas tab.
Step 3: Inside the Formulas tab, and click the AutoSum dropdown button and select Average formula.
Step 4: Once you click the Average, average for the selected cells will be calculated and displayed in the below cell.
It was the normal average calculation using Excel AVERAGE() formula.
Calculate Average Weighted
This time, we will calculate the average weighted by using the SUM and SUMPRODUCT function of Excel, as we already discussed earlier. Its process of calculation is completely different than the simple average calculation.
Average weighted requires values and their corresponding weights. The SUMPRODUCT function is actually worked like – (45*1)+(38*2)+(41*3). Let’s take an example to calculate the average weighted.
Example 1
Step 1: We have the following data with values and their weight, for which we will calculate the average weighted.
Now, we will use the SUM and SUMPRODUCT functions to find out average weighted.
Step 2: Firstly, use the SUMPRODUCT function and pass the range of scores and range of weight as an argument.
=SUMPRODUCT(B2:B4, C2:C4)
Step 3: Keep the calculated result in a cell that will be further used for calculating the average weighted.
Note: The SUMPRODUCT function is performed this calculation in such a manner: (45 * 1) + (38 * 2) + (41 * 3) = 370.
Step 4: Now, calculate the sum of the weights by using the following formula.
=SUM(C2:C4)
Step 5: Press the Enter key and keep the result in a cell.
Step 6: Now, use the calculated result in step 3 and step 5 (that is stored in C6 and C7 cells respectively) to calculate the average weighted.
=C6/C7
Step 7: Hit the Enter key and get the average weighted for the data given above.
In this way, the average weighted can be calculated for the set of data.
Example 2: When weights add up to 100%
Take some data in Excel having marks of five subjects along with the weights in percentage instead of a number. We are having this score and weight in percentage, as shown below.
Here, you can see that the total of all weight is 100%. Now, let’s learn to calculate the average weighted for this data.
Step 1: Firstly, calculate the sumproduct for the marks by using the SUMPRODUCT() function.
=SUMPRODUCT(B2:B6, C2:C6)
Step 2: By hitting the Enter key and get the calculated result for the sum product.
Step 3: Now, use the following SUM() function to calculate the sum of weight.
=SUM(C2:C6)
Step 4: One more time, hit the Enter key and get the result, i.e., 100%.
Step 5: Now, divide the calculated sumproduct by the sum using the following formula.
=E3/E4
Step 6: See that you will find that the average weighted is same as the calculated sum product, which is 43.76.
This is because the weights adds up to 100%. It means the sum of the weights is 100%.
Example 3: When weights not add up to 100%
In the above example, the weights are assigned in such a way whose total sum was 100%. Now, this time we will give you an example whose weights sum is not 100%. But all the time, it might not be possible that always all weights sum is 100%.
For this example, we will take some data in Excel having marks of five subjects along with the weights in percentage instead. This data is as created below.
Here, you can see that the total of all weight is not 100%, as in the above example. Now, let’s learn to calculate the average weighted for the data when weights are not added up to 100%.
Step 1: Firstly, calculate the sumproduct for the marks by using the SUMPRODUCT() function.
=SUMPRODUCT(B2:B6, C2:C6)
Step 2: By hitting the Enter key and get the calculated result for sum product.
Step 3: Now, use the following SUM() function to calculate the sum of weight.
=SUM(C2:C6)
Step 4: One more time, hit the Enter key and get the result, i.e., 130%.
Step 5: Now, divide the calculated sumproduct by the sum using the following formula.
=E3/E4
Step 6: See that the average weighted is calculated now, which differs from the sumproduct as in the previous example.
Example 4 – When the Weights Need to be Calculated
This example is a bit different than the other examples discussed above. For this example, we have taken the real-life scenario data that you need to solve in your daily life.
You will notice that weights are also specified in this data. Sometimes, it might also be possible that the weights are not directly available. In such cases, you may need to calculate the weights first and after that calculate the weighted average.
Suppose you have three different types of products details as mentioned below:
You can use the following SUMPRODUCT and SUM formula to calculate the weighted average price per product. Here is the formula you can use:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)
In this formula, we are dividing the SUMPRODUCT result by the SUM of weights (quantities) to make sure that the weights add up to 100%.
Now, press the Enter key and get the calculated average weighted result.
