In this article, we’ll look at different ways of how to find weighted average in excel using SUM & SUMPRODUCT function.

In one of our earlier articles, we learned how to calculate average in excel. But let’s say when calculating average some values have more weight or weightage than others. These values contribute more to the final average. In such scenarios we calculate weighted average in excel.

One of the best examples of weighted average is when a teacher calculates finals marks of a student based on different assignment submissions, viva, exams, practical etc. Where each evaluation has different weightage.

There is no specific weighted average formula in excel, instead we have to use some other functions to perform weighted average calculation in excel.

What is weighted average?

It is a type of arithmetic mean where different values carry more importance than other values.

Aside from calculation of grades, weighted average formula is used in

1. Product Costing
2. Financial Investments
3. Inventory Management & Costing
4. Others

One calculates weighted average by multiplying each value by its designated weight. After which we add up all the values and divide the sum by sum of all weights.

Let’s look at Excel weighted average with a simple example. We have the following data of a student:

To calculate weighted average, we need to first multiply each evaluation’s marks with corresponding weighted percentage. Then add up the all products together and divide it by sum of the 4 weights.

=((43*0.1)+(67*0.2)+(85*0.3)+(72*0.4)) / (0.1+0.2+0.3+0.4)=72

Normal average would be 66.75 and weighted average calculation would be 72.

Calculating Weighted Average in Excel

In excel, we would need to use the same approach we saw above with the help of some functions. Let’ see how to do weighted average in excel:

Method 1: Calculating Weighted Average by using SUM Function

If you know how to use the basic SUM function then just follow the formula below. We’ll use the same dataset as earlier for our example.

Type =SUM(B2*C2, B3*C3, B4*C4, B5*C5)/SUM(C2:C5)

It’ll perform the same calculation as above. Just look at image below to see the weighted average formula in excel:

SUM Function works very well when you have a small set of numbers to average. But in case you have a large data set you can’t manually type such a big formula. To overcome this, we use SUMPRODUCT Function.

Method 2: Find Weighted Average in Excel using SUMPRODUCT Function

When dealing with a large set of data Excel SUMPRODUCT Function works very well. Instead of multiplying each value by its weight individually, you can tell SUMPRODUCT to multiply the 1^st value in array 1 with the 1^st value in array 2 and so on. After multiplying it will add up all the products and returns sum. Let’s see this in action:

Type =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C6)

First the SUMPRODUCT function multiplied each value in array 1 i.e. column B values with each corresponding value in array 2 i.e. column C. Then added up all the products. The result was then divided by Sum of all weights i.e. sum of C2 to C5.

Key Points about Excel Weighted Average

1. When calculating Excel weighted average, the weights do not have to be in percentages or add up to 100%.
2. You can make a priority scale and assign a certain number of points to each item. See the image below:

10 being the highest importance.

Conclusion

That was all about Excel formula weighted average. Do check Excel SUM & SUMPRODUCT to better understand the calculation of weighted average.

I hope this article was helpful to you and explained you in depth how to calculate a weighted average in excel and much more.

If you have any questions or tips, I’d love to hear them in the comment section below.