Calculating weighted average is the bread and butter of most teachers. You need to be able to provide students with grades that are weighted differently for homework, tests, and quizzes when using a curved system. Most often, homework is weighted less than tests and calculating that can be difficult and time-consuming. Not only is a weighted average function useful for this reason but it can also help you in a variety of other subjects as well, not just the academic. You can learn how to use this handy function by downloading this free template and following along with the free guide below as well.
Using the Weighted Average Template
The free template file is a simplified example of how you would incorporate the weighted average function into your own excel template to keep track of each students grades.
In the template provided, you will see two tables. One with the data you would take for students and the different homework, test, and quiz information and a table for the weighted averages off to the side.
The first column gives you subjects in which the function can refer to, then a score, and finally the percentage you want to use for weighted averages. The last two columns of the first table display the normal average and the weighted average for a certain test, quiz, etc.
To get the normal average, you would use the following formula.
=AVERAGE(D5:D7) (with the range being the score for that subject).
If you need the weighted average as well, you would then apply this formula,
=SUMPRODUCT(D5:D7*E5:E7)/SUM(E5:E7) (note that the ranges here are used for the math subject in the example and the sum range refers to the weighted percentages you have already chosen for that subject, (Theory=75%, Practical=20%, Homework=5%).
The table to the right uses the same formulas, but simply expands the ranges to include all subjects instead of just one.
You can see how easy calculating weighted averages can be with this simple template.
Download: Weighted Average
Check out this offer while you wait!