When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: How to Calculate Weighted Average. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "This video will demonstrate how to calculate a weighted mean, also known as a weighted average, using the SUMPRODUCT formula in Excel. There are times when it is helpful to weight a set of data before taking the average. The data set may be composed of similar items which have different valuations, and taking a simple average does not suffice. In this data set, for example, we have 12 homes represented. The Size column is thousands of square feet per home sold. 1.4, therefore, is a 1,400 square-foot home. The selling price is also in thousands, which means that the 1,400-square-foot home sold for $100,000. If we'd like to understand the average selling price per square foot, it would be helpful if we first found the total amount of dollars spent by the total amount of square footage and then got the average so that we have a weighted average per square foot. We can easily do this by calculating the weighted mean in Excel. I'll demonstrate with the longer method, and then show how it can be done using SUMPRODUCT. First we need to add a new column. Column 'C' we'll call 'Size by Selling Price.' And for this column we're going to multiply size times selling price."
Equals A2 times B2 is entered to cell C2.
Narrator: "I want to do that all the way down the column. Now that I've got the first formula in, double-click and it will carry my formula down the column."
With cell C2 highlighted, the bottom right corner is double-clicked to automatically apply the equation to the remainder of the column.
Narrator: "Now I sum that column using, on the Home tab, this Sum feature."
With cell C14 selected, the Sum button is found in the Editing section of the Home tab. When clicked, a drop-down list appears, and Sum is selected. The equation is automatically applied to the cell, summing cells C2 to C13.
Narrator: "And I also need to sum the Size column."
Cell A14 is selected, and the Sum button is clicked.
Narrator: "Now, to get the weighted mean, I need to divide my size-by-selling-price total by the total number of square feet. All of these are in thousands, and the final number I get will be dollars per square foot. My weighted mean is 1,344 divided by 13.8, which is about $97 per square foot."
Weighted mean is entered into cell B15. Equals C14 divided by A14 is entered into cell C15.
Narrator: "Now let's look at how we do this using SUMPRODUCT."
Text: Sumproduct does the work of our "column C" - multiplying size x selling price all the way down the column, and adding up the results.
Narrator: "SUMPRODUCT will do for us what we did in column 'C.' It will multiply size by selling price all the down the two columns, and add the resulting products together. =SUMPRODUCT(. Notice it's telling us that we need to use an array here."
With the formula partially typed, a box below the cell lists suggestions to complete the formula.
Narrator: "That simply means that we're going to be performing operations on multiple rows or multiple columns rather than individual cells. So, I've captured the Size column. Now I'll capture the Selling Price column. Close parenthesis. 'Enter.'"
The formula in cell C16: Equals Sum product left parenthesis A2:A13 comma B2:B13 right parenthesis. Text: Notice the comma between the cell ranges? Each cell is an 'array' in the formula.
Narrator: "And you can see that the result, 1,344, matches what we had already found. To get the weighted mean, divide it by 13.8, and we have the same result. Our weighted mean is $97.39 per square foot. Another common use for a weighted mean is to calculate the grades for a class."
Additional data (Graded Item, Points, and Weight) is added to columns E to G.
Narrator: "In this table, you can see points earned by a hypothetical student for various assignments, and the weight for each graded item. For example, the first three exams are worth 10% each for the final grade, and the final is 25%. The project is 20%, and homework totals 25%. That gives us the 100% total bucket of points. All of the assignments have been scored out of 100 points. If we only add the raw points up and divide it by 1,000, then the student would have a score of 80. 1%. That's their raw score here."
Cell F13 lists the Raw score, 80%.
Narrator: "However, when we assign weights, giving more weight to the exam scores and to the project than the homework, the student's score drops to a 76%. This is a second use, therefore, of a weighted average. SUMPRODUCT has been used here to multiply points time weight all the way down the columns, and then divided by 100, indicating that we now have a weight out of 100%. When we divide the total number by 100, we get .76, or 76%. Two cautions with SUMPRODUCT—all data must either be in rows or in columns. We cannot mix and match rows with columns. And both rows or both columns must have the same number of data values. If those two criteria are met, SUMPRODUCT is a useful tool for quickly summing the product of two data ranges, and from there calculating the weighted average."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.