A weighted average is a type of average that assigns different weights or values of importance to each element in a dataset. Unlike a simple average that treats all elements equally, a weighted average adjusts the contribution of each element based on its relative significance. This means that some elements have a greater impact on the final result than others, depending on their weights.
Why use a weighted average?
Weighted averages are useful because they provide a more accurate representation of the data by taking into account the importance of each element. For example, in financial analysis, a weighted average may be used to calculate the average interest rate of a portfolio of loans or investments, where the weight of each loan or investment is based on its size or duration. In schools, a weighted average may be used to calculate a student’s overall grade by assigning different weights to assignments, quizzes, and exams based on their importance or difficulty. Anytime you don’t want everything to have the same weighting or importance is when you’ll want to use a weighted average.
Calculating a simple weighted average in Excel
A common way to apply a weighted average is by using a points system. Suppose you are looking to buy a house and have many different criteria that you want to take into consideration, such as square footage, location, if it has a basement, etc. But not all of these items are equally important, and so you may want to say that location is worth 30 points and square footage is worth 25 points, and so on.
The first step is to assign a weight, or point value, to each one of these criteria. Then, assign a score to each one of them criteria, perhaps within a range of 1 to 100. Once you’ve done that, you multiply the score by the points. Total that up, and divide it by the total points, and you’ve got your score, or weighted average. Here’s an example:
This particular house scored high on the most important items, and thus, resulted in a high weighted average. The total of the score x points column was 10,190. Taking that value and dividing it by 145, the total points, results in a weighted average of 70.28.
Here’s another house, which scores far lower, with a weighted average of just 45.48:
Although it scored high on areas such as school and kitchen, because of its low scores on the top two weightings — location and square footage — that kept its weighted average down.
Creating a template like this in Excel and comparing your different scores can be a way to help compare houses and other things, while giving each criteria an appropriate weighting. By simply scoring everything on a value of 1-100 without weighting, the problem would be that each criteria would effectively be equal, saying that things like layout and the garage are just as important as the location and size of the house, which most people likely wouldn’t agree with. By using weights, you can better take into account the value of each individual criteria.
Calculating grades using weighted averages
Another use for calculating weighted averages is when it comes to grading. In a class, you might have a specific weighting scale that says assignments are worth 10% of your grade, quizzes are 20%, a project is worth 5%, a mid-term is 25%, and the final exam accounts for 40%.
In this case, you’re using percentages that add up to 100% rather than weights, which may be more subjective. This still works in largely the same way as you are multiplying a score by the weight. Except now, since the weights add up to 100%, you don’t need to worry about taking the total and dividing it by the total weights. Whatever your result is, that is the total score. Here’s an example of how a student scored in a class:
When using percentages for weighting, it’s important to double check they add up to 100% to ensure everything is accounted for. In this example, the student had a score of 72.25, which would be the same as saying they scored 72.25%, which would be their grade for the course. As you’ll notice, the student’s high scores on the quizzes and mid-term exam were unfortunately offset by a poor final exam mark.
In this example, since we’re just looking at percentages, you can do without the extra column for value, which takes the weight x the score. Instead, you can use SUMPRODUCT. If the weightings are in cells A2:A6 and the scores are in B2:B6, the grade can be calculated with the following formula:
The formula will multiply each value by the corresponding value in the same row, thereby eliminating the need to use an extra column. By using an Excel formula, you can save yourself the extra step of having to tally up the values and then dividing them by their weights again.
If you liked this post on How to Calculate Weighted Average in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.