Enhance your data analysis skills by mastering Excel's SUMPRODUCT function to effortlessly calculate weighted averages. Learn how to apply conditions to refine calculations for specific criteria, such as STEM departments.
Key Insights
- The SUMPRODUCT function efficiently computes weighted averages by multiplying corresponding values from multiple arrays and summing the results—demonstrated by calculating average salaries weighted by the number of professors.
- Adding conditional logic within SUMPRODUCT, like a double-negative operator to filter STEM departments, allows precise analysis, shown by isolating a total STEM salary of $33,750,000.
- Weighted averages can significantly differ from simple averages due to influential data points; the university's weighted salary average increased to $116,000 primarily because two departments with the highest faculty counts had salaries of $175,000 and $100,000.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
This topic is called statistics according to the worksheet tab at the bottom, but it's basically an overview of the sum product function and its ability to calculate weighted averages with conditions. So let's take a look at our exercise here. We can pretend that we're dealing with a university that has several departments.
Each of those departments has professors and the average salary for the professors of those departments are listed in the average salary column. We also have the number of employees, and we can think of this as the number of professors in each department. We also have a classification for each department as either being part of STEM or not.
If it's part of STEM, we'll see a Y. If that department is not part of STEM, we'll see N. And then we have the salaries. Basically, the average salary multiplied by the number of employees, and that gives us the average salaries for each department and each employee. So the first thing we want to calculate is what is the average salary for the entire university? Well, you can take all the salaries here, and you can come up with an average.
In this case, this is the mean. If I press F2, that is the average of the population when it comes to salary, average salary of the population. I'll press ENTER.
Next thing I would like to know is what is the weighted mean or weighted average? Weighted average takes into account the number of professors or employees who made a certain amount, whereas an average just looks at the average salaries individually. It doesn't take into account the number of employees who made said salaries. So to calculate that, we're going to take the total for the salaries, and we're going to divide that by the number of employees.
When I press ENTER, I will see 116,000. That is definitely higher than the regular average. Why is that? Because when you take a look at the number of employees and the amounts they made, 75 and 100, which are our top two counts for the number of employees, made 175,000 and 100,000.
So their weight contributes to the average weighing more towards the 116,000 side of our calculations. Now we want to figure out the same information that we returned here. In order to come up with the salaries and the total salaries, what we did is we multiplied the average salary by the number of employees.
And we did that all the way down the line. Eventually, after multiplying those values, we then added them up, and we got the total. Well, you could do the same thing with some product, and all you would have to do is select the two columns that are used in the calculation.
So this is going to be equal to SUMP. After I type SUMP, I can then press TAB. And then I'm going to select all the salaries, all the average salaries, comma, and then I'm going to select all the number of employees.
Now, if I did this correctly, I should get exactly the same amount that I see underneath the salaries, which is 36,310,000. This is just another way to come up with the total salaries. I'll go and choose currency, and I'll decrease the decimal.
And that is exactly the same. Now I'm interested in getting the total salaries for those departments that are part of STEM. So I want to take into account the column where Y is included as the value for STEM.
So equal SUMP. Now I'm going to select all the salaries. I'm going to enter a comma, and I'm going to select all the number of employees, just like I did earlier.
But now I also want to include the column that contains text. In order to do this, I'm going to enter a double negative sign, open parentheses. Now I can select that column that contains text and say that I'm looking for values that are equal to double quotes Y, close double quote, close double quote.
So this is going to give me the value only for salaries, average salaries, where the STEM is equal to Y. So I'll press ENTER. Okay, let me just take a look here and see what I did. F2, double negative equal to, oh, I included a closing parentheses after E11.
That actually should be at the very end. All right, so now I'll press ENTER. There is my value.
I'm going to go to the format painter, apply the currency formatting, $33,750,000. Now, one way to validate this is the actual value is to look at the salaries that we have here and eliminate anything that is not Y. And there are those two values here. I'll delete them.
And you can see it matches what I calculated. I'll press control Z to undo. And then the last calculation, we want to get the weighted mean of the STEM.
We're interested in the salaries, but based on the number of people who got those salaries. So this is going to be equal to, now I don't have to rewrite the formula above. So I'm going to select that value and I'm going to divide it by some ifs.
When I type some ifs, I only want to count where this column contains values where the STEM is equal to Y. So I'm going to select that, enter a comma and then enter double quotes Y and then double quotes, close parentheses. Now the count is only going to be related to number of employees where STEM is equal to Y. I'll press ENTER 122,000. And so that makes sense because when I take a look at the salaries for Y, they are on the higher end.
And it also includes 175 and 100,000. Now the number of people who got 175,000 are 100. So those 100 people weigh more when it comes to the average.
So therefore weighted average. That's why it's getting closer to above 120 because 100 of those employees made a 175. The next highest amount is 100,000.
So this is basically an overview of how you can calculate a weighted average using the SUMPRODUCT function and additionally SUMIF if necessary. Thank you for watching.