If you don’t know how to calculate the weighted average in Excel, we are going to help you out. In Excel, it is very easy to determine the weighted average of a series of values just by using a couple of formulas.
What is the weighted average?
The weighted average differs from the simple average. The weighted average considers the weight or importance that a value has with respect to another, or with respect to the total.
How to calculate the weighted average in Excel?
The typical example of a weighted average is a college midterm grade. The first midterm grade may have a value of 30%, the second midterm grade a value of 30%, and the third midterm grade a value of 40%.
In this case, a 4 on the first midterm weighs much less than a 4 on the third midterm. A 0 on the first midterm can be made up by a 5 on the final midterm.
Suppose you need to determine the average salary of an employee of a company with the following information:
| Salary | Number of employees with that salary |
| 700,000 | 30 |
| 1,000,000 | 10 |
| 1,800,000 | 5 |
| 2,500,000 | 3 |
| 5,000,000 | 1 |
| 8,000,000 | 1 |
There are 50 employees and there are 6 salary ranges. A simple average would be to take the sum of all wages and divide them by 6 and the result would be: 19,000,000 / 6 = 3,166,667.
To assert that each employee of that company earns an average salary of more than 3,000,000 is incorrect since the vast majority barely earn 700,000. A great way to trick the reality.
Anyone who sees such information will believe that it is the best company to work for, but the truth is that it is only good for bosses who earn a lot, but not for workers who barely earn to survive.
To determine a much more realistic value, each result must be weighted according to its importance or weight, or according to its participation in the total sample.
To do this in Excel we use the SUMPRODUCT function together with the SUM function.
Assuming that the value of salaries is in the range A1: A7 and the number of employees with each salary in the range B2: B7, the formula to be used will be: =SUMPRODUCT(A2:A7*B2:B7)/SUM(B2:B7)
This formula gives us a weighted average of 1,210,000, a much more realistic value than 3,166,667.
What the formula does is take the salaries and multiply them by the number of employees with each salary and add the result, and then divide this result by the total number of employees.
This way you’ve learned how to calculate the weighted average in Excel, why don’t you try it out now?
