How to Calculate the Weighted Average in Excel (using the function SUMPRODUCT)

How to Calculate Weighted Average in Excel

This tutorial offers a quick and easy way how to calculate a weighted average in Excel.

Are you curious to learn more?

Would you rather watch this tutorial? Click the play button below!

What is the Difference between Weighted and Regular Average

Weighted average is a calculation standardly used if the values for calculating an average have a varying degree of importance. For weighted average, the degree of importance for each value is taken into account. Just like in our example, where the score for each exam has a different weight.

How to Calculate the Weighted Average in Excel - varying degree of importance

So, here we’ve got the score values and for each value, we’ve got the information on how important it is, which is expressed in per cent.

How to Calculate the Weighted Average in Excel - Score Values

How to Calculate the Average in a Standard Way

When calculating the regular average, each value we use for calculation is equally important, they have the same weight. To calculate the average in a standard way, we can use the function ‘Average’. We simply enter the values to be used in this calculation and it’s as easy as that. To learn more about this function, EasyClick Academy prepared a separate tutorial the link to which you can find in the list below.

How to Calculate the Weighted Average in Excel - regular average
How to Calculate the Weighted Average in Excel - regular average result

But in today’s tutorial, we’ll focus on how to calculate the weighted average. For this calculation, as you might already suspect, we’ll need to use a different function, specifically SUMPRODUCT.

How to Calculate the Weighted Average in Excel - SUMPRODUCT

Let’s have a closer look at this function and see how we can calculate the weighted average of these values using SUMPRODUCT.

How to Calculate the Weighted Average Using the SUMPRODUCT Function

First, click into the cell where you want to calculate the weighted average. Enter the equal sign and start typing ‘SUMPRODUCT’.

Excel will look it up, so we can simply click on it and fill in the data.

How to Calculate the Weighted Average in Excel - SUMPRODUCT function

First come the score values, the exam results from column C. We select all values we want to include in the calculation, so we highlight the whole area with these figures. As you can see, Excel entered the coordinates of the selected area into the formula.

How to Calculate the Weighted Average in Excel - Score numbers

Now we add a comma, and the data on weight of particular values should follow. Again, we select the whole area marking the weight of the values, and again, Excel included the reference to the whole range in the formula.

How to Calculate the Weighted Average in Excel - weight percent

As soon as we’ve got the Score and the Weight data defined, we close the brackets and move on to the next part.

How to Calculate the Weighted Average in Excel -  close brackets

Here, we need to divide the whole calculation with the sum of all Weight values we’re working with. So, we’ll enter the division slash, type in ‘SUM’ and fill the data from the section ‘Weight’ in the brackets again.

How to Calculate the Weighted Average in Excel -  divide the whole calculation

We close the brackets, hit ‘Enter’ and that’s all it takes!

How to Calculate the Weighted Average in Excel -  weighted average result

Here’s the weighted average calculated in a quick, easy and effective way, which works with any amount of data you need to process.

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.