If you are looking to calculate the mean of a group of numbers, you can use the average formula in excel. However, you might find that an average for a list of numbers could lead to errors in your excel document. The reason for this is that this formula could count each value as one. Sometimes you might have something that is called weight which is added to that number that will throw off a typical mean formula. To get started, you will need to have at least two columns that have the numbers you want to average out and the weight that is associated to it. Let’s explore this further to see what this actually means.
Let’s say we are a manager of a company and we want to see the average salary for all the employees on our team. In this example, we have a total of 363 employees. By simply averaging out the salaries in excel, we are left with an average salary per employee of over ninety thousand dollars. That would certainly raise some eyeballs as 55% of the company falls into the eighty-five thousand range. Something would definitely seem wrong and you would be confused since the formula did it’s job.
Here is that formula below.
How to use the SUMPRODUCT Function to find the Weighted Average in Excel
The picture above shows the incorrect average of the salaries due to each salary being counted as one employee. This number would be true if there were only five team members at this company and that was their salary. Since we know that we have a team of over three hundred, we will need to fix this number to represent everyone.
To solve this problem, we are going to rely on the SUMPRODUCT formula to match multiple criteria points to help give us the correct average for all three hundred employees. In a nutshell, we are counting each salary times the amount of people who make up that salary, and then dividing all of those employees into that number.
Here is the formula with how to calculate the weighed mean
You need to start the formula off with =SUMPRODUCT (criteria 1 , criteria 2) / SUM (criteria 2)
Criteria 1 represents column B in this example below. We want to add up all the salaries listed below. Criteria 2 will add up all the employees that make up those salaries. We then divide that number by the total number of employees to get the average.
This formula allows you to add weight to an average which allows you to save time rather than do all the math-out by hand.