Often you may want to assign weights to variables in Excel when calculating an average.
For example, suppose students in some class take three exams over the course of a year and each exam is weighted accordingly:
- Exam 1: 20%
- Exam 2: 20%
- Final Exam: 60%
To calculate the student’s final score in the class, we would use the following formula:
- Final Score = Exam 1*0.20 + Exam 2*0.20 + Final Exam*0.60
The following example shows how to calculate this weighted average in Excel.
Example: How to Assign Weights to Variables in Excel
Suppose we have the following dataset in Excel that shows the exam scores of various students in some class:
Suppose we would like to calculate each student’s final score in the class using the weights specified for each exam.
We can type the following formula into cell E2 to do so:
=SUM(B2*0.2, C2*0.2, D2*0.6)
We can then click and drag this formula down to the remaining cells in column E:
From the results we can see:
- Andy has a weighted final score of 83.
- Bob has a weighted final score of 91.6.
- Chad has a weighted final score of 92.4.
And so on.
Note that we could also assign the weights for each exam in row 2 and then use the following formula in cell E3 to calculate the final weighted score for each student:
=SUM(B3*$B$2, C3*$C$2, D3*$D$2)
We can then click and drag this formula down to the remaining cells in column E:
Notice that the final weighted scores for each student match the ones calculated in the previous example.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Weighted Percentage in Excel
How to Find Weighted Moving Averages in Excel
How to Calculate Weighted Standard Deviation in Excel
