excel formula weighted average of percentages

#Excel formula weighted average of percentages pro
#Excel formula weighted average of percentages download

So for Assignment 1 it multiplies 85 by 5, and for the Test it multiplies 83 by 25. In other words, it finds the sum of the products, which is where it gets its name. SUMPRODUCT is multiplying (finding the product of) each assignment's grade times its weight, then adding all of the products together. Let's look at each part of this formula to see how it works, starting with the SUMPRODUCT function. That's it! When you press Enter on your keyboard, Excel will calculate the weighted average. Remember to close the parentheses after the argument: We just need one argument for the SUM function: the cell range C2:C9. Start by typing a / (forward slash) for division, and then type the SUM function: Later, we'll talk about why this is important. This part will divide SUMPRODUCT by the SUM of the weights. Now we'll add the second part of our formula. When you're done, type a closed parenthesis: You'll need to use a comma to separate these two arguments. The second argument will be the cell range C2:C9 -the cells that contain the weights. In our example, the first argument will be the cell range B2:B9 -the cells that contain our grades: SUMPRODUCT can have any number of arguments, but it will usually have two. Next, we'll add the arguments to the function. Because the arguments will be in parentheses, go ahead and type an open parenthesis: The first part of our formula will be the SUMPRODUCT function. As with any formula, start by typing an equals sign (=). Now that we have our spreadsheet set up, we'll add the formula to cell B10 (any empty cell will work).

That's OK because our formula will still work no matter what the weights add up to.

But in this case, the weights actually add up to more than 100%. You can think of the weight as being the percentage of the final grade. A higher weight will cause the assignment or test to have a greater effect on the final grade. The second column (column C) contains the weights. The first column (column B in our example) contains the grades for each assignment or test. In order to calculate the weighted average, you'll need at least two columns.

#Excel formula weighted average of percentages download

If you'd like to follow along, you can download our example. The method we're using works with all versions of Excel, and it also works with other spreadsheet applications like Google Sheets.

#Excel formula weighted average of percentages pro

Even if you've never used SUMPRODUCT before, you'll be able to use it like a pro by the end of this article. For these situations, you'll need to calculate the weighted average.Īlthough Excel doesn't have a weighted average function, it does have a function that does most of the work for you: SUMPRODUCT. But what if some of the values have more 'weight' than others? For example, in many classes the tests are worth more than the assignments.

en/excel-tips/how-to-use-excels-vlookup-function/content/ Use SUMPRODUCT to calculate weighted averagesĮxcel makes it extremely easy to calculate the average of several cells: Just use the AVERAGE function. Lesson 20: Use SUMPRODUCT to Calculate Weighted Averages