- #Calculate difference in months in excel for mac pro
- #Calculate difference in months in excel for mac download
For example, if we increase one or more of the weights, the second part of the formula simply divides by a higher number, bringing it back down to the correct answer. Remember that the weights don't need to add up to 100%? This is because this part of the formula takes care of that for us. The second part of the formula is actually really useful because it allows the formula to automatically correct its calculations. This part brings the value back down to a normal grade range, making the answer 83.6. This is where the second part of our formula comes in: /SUM(C2:C9). Luckily, we don't have to write a long formula like this because SUMPRODUCT is doing all of it automatically.īy itself, SUMPRODUCT will give us a huge number: 10,450. If you could look "under the hood" of the SUMPRODUCT function, you would see that this is what it is actually calculating:
By comparison, a "regular" average would count each assignment one time, so each one has the same weight. This is why the Final Exam has a bigger impact on the final grade. For example, Assignment 2 is counted 5 times, but the Final Exam is counted 45 times. If you're wondering why the values needed to be multiplied in the first place, think of it this way: The assignments with a higher weight are counted more times. 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.
#Calculate difference in months in excel for mac 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.
#Calculate difference in months in excel for mac 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
0 kommentar(er)
