I am working on a weighted average problem that I am stuck on. It is a little different than most weighted average questions I have read on here so I am going to be very specific with what I am looking for. I have made a mock data set to illustrate the problem.
| Person | Subject | Grade |
| Bob | Math | 22 |
| Bob | English | 45 |
| Bob | Math | 38 |
| Bob | Math | 86 |
| Bob | English | 54 |
| Bob | Science | 34 |
| Sarah | Math | 56 |
| Sarah | Science | 75 |
| Sarah | English | 34 |
| Sarah | Math | 22 |
| Sarah | English | 63 |
| Sarah | Science | 22 |
Here is the data set, a class room of 2 students and their attempts on test for different subjects. Now I want to find the variance of a students grades compared to the class average in this way. Lets say it was looking at bobs grades:
| Subject | Number Of Tries | Bobs Average Grade | Class Average Grade |
| Math | 3 | 48.7 | 44.8 |
| English | 2 | 49.5 | 49 |
| Science | 1 | 34 | 43.7 |
| Subject | Weight | Grade Difference | Weight * Difference |
| Math | 0.5 | 3.9 | 1.933333333 |
| English | 0.33 | 0.5 | 0.166666667 |
| Science | 0.17 | -9.7 | -1.611111111 |
So the weight for math is 3 / the total number of attempts, 6 for .5. The difference is Bobs average grade - the class average.
And then you multiply them together for the variance (weight * difference). Summing all these variances together gives you .48. This is the number I want to return.
Can any provide guidance? I feel like I have gotten close but can never return the expected numbers.
