As promised last week, today I’m going to talk about how to use a spreadsheet to calculate weighted averages in general, and Grade Point Averages (GPA) in particular.*
A weighted average is just any average where some values are more important. Values with greater importance are said to be weighted more heavily. A GPA is the perfect example of that.
Let’s start by looking at a hypothetical college transcript, maybe from a student who wasn’t that great at math. If you gather all the grades from a semester and put them in a list, it might look something like this.
Anyone who’s in Calculus can probably figure out the simple average of their grades, which is gotten by adding up all the grades and dividing by the number of classes. Since there’s one row per class, and the average() function divides the sum of the numbers by the number of rows, you can use that function to figure out the simple average of the grades, which is 3.0 — a B average. If all the classes had the same number of credit hours, this would even be true. However, some classes have more hours than others, and thus should have a greater effect on the average.**
To account for this, you multiply the grade by the hours (weight) of the class, then divide by the total number of hours instead of the total number of classes. This gives you:
1*4 = 4
3*3 = 9
4*2 = 8
3*3 = 9
3*3 = 9
The sum of those products is 39, and the sum of the hours is 14, so
36/14 = 2.78 which is a C average instead of a B. That’s why some job applications will ask for the in-major GPA instead of or in addition to the overall GPA — they assume that most people will do better in their actual field.
One extra note — weighted averages aren’t always lower than simple averages! If the hypothetical student had been a math geek (like me), and gotten an A in Calculus and a C in Swimming, then their GPA would be 1*2+3*3+4*4+3*3+3*3 / 14 = 2+9+16+9+9 / 14 = 45/14 = 3.21, which is higher than the simple average of their grades.
Some examples of weighted averages other than GPAs might be:
- Teaching impact. If a librarian had a one-on-one instructional session with a student, and that student rated their experience as 5 out of 5, that certainly looks better on the statistics sheet than the 4 out of 5 the teacher might have gotten from a classroom session with 9 students. However, if you’re trying to show the impact of the teaching program to a bunch of administrators, they won’t be impressed that you managed to make one student very, very happy. They’ll care more about the overall results, which means getting a weigthed average of the results per student, rather than session. 9*4 + 1*5 = 36 + 5 = 41. 41 / (9+1) = 4.1 overall rating.
- Service statistics. If person A helped 15 people during one shift, and 20 during another, their per shift average would be 17.5. If person B only helped an average of 5 people per shift, then the simple average makes person B look like a slacker who’s dragging down the department’s average productivity. However, if person A’s shifts were 5 hours long, and person B only had 1 hour shifts, then which one is really being more productive? This example is a little weird because in this case, since you’re interested in how much they did per hour instead of how much they did overall, you shouldn’t just multiply by the number of hours. If you did, person A would have somehow helped 87.5 people. Instead, you’re looking at how much person A did during 1 shift divided by 5 hours, which is to say a weight of 1/5. For person B you want 1 shift divided by 1 hour, so 1/1, which is a weight of 1.
- Class grade Suppose tests are 30% of the final grade, homework is 50%, quizzes are 15%, and classroom participation is 5%. If you know what your past scores were, and can guess how much your teacher is likely to give you for classroom participation, you can use weighted averages to figure out what your final grade is before it’s posted. You can also use weighted averages to work backwards and figure out what you need to get on the final in order to get the grade you want, but that’s a post for another day.
Instructions for calculating weighted averages
So, in general, here’s how to calculate a weighted average.
- Choose three columns. These can be any three. They don’t have to be next to each other, but it might make it easier to see what’s going on if they are. You might want to have a fourth column that has labels, like the “Class” column in the GPA example.
- Label the columns “values”, “weights”, and “products”. Once you’re comfortable you can change the names, but that’s how I’ll be referring to them for the rest of these instructions.
- In the “values” column, enter the base, unweighted values. In the GPA example, that’s the grade. In the teaching example it’s evaluation score, in the service example it’s the number of people helped, and in for the class grade it’s the average of your scores in that category.
- In the “weights” column, enter how many times this should be counted. In the GPA example, that’s the credit hours. In the teaching example it’s the number of students, in the service example it’s 1 divided by the number of hours worked, and in the class grade it’s the percentage of the final grade that section counts as.
- In the “products” column, multiply the other values together. For example, if your columns were A, B, and C respectively, then in C2 the formula would be =A2*B2.
- Add up the values in the “values” column. You can do this manually (i.e. a2+a3+a4+a5+a6) or by using the sum function (i.e. =sum(a2:a6).)
- Add up the values in the “poducts” column.
- Divide the sum of the products by the sum of the values.
* If you’re a new graduate who’s reading this you might want to keep reading despite the fact that your transcript will tell you your overall GPA. If employers want your particular degree, not just just any college graduate, then they might want your in-major GPA. That’s not something which most transcripts tell you. (back)
** If you doubt that this should be the case, imagine if you were asked to do a huge project at work that took weeks to finish, and a co-worker was asked to do something simple that took an hour, do you think your efforts should be gauged equally? (back)