Weighted Average

[jrmathes]

Can someone please help? My daughter has a 63 average and I'm trying to confirm her grade is correct. I can not figure out the formula...

I have the formula to figure the straight average (last column) but now how to I implement the weighted %ages (first column)? Thank you!!!

10% - Classroom = 56
5% - Homework = no grade
10% - Participation = no grade
20% - Projects = 40
15% - Quizzes = 76
10% - Reports = no grade
30% - Tests = 75

 

[barry houdini]

Hello jrmathes, welcome to MrExcel

Assuming that the weights are in A2:A8 and the scores in C2:C8 then you can get the weighted average like this

=SUMPRODUCT(A2:A8,C2:C8)/SUMIF(C2:C8,"<>no grade",A2:A8)

I assume that's the calculation being done because that gives me 63.33 for your example. The weights are effectively "pro-ratad" up to make up for the "no grade" subjects, so because there are scores in only 75% of the subjects (by weight) then each score is effectively multiplied by the relevant percentage and also 1/75% = 4/3

 

[Ron Coderre]

Your daughter's instructor seems to be very generous

This is the basic scenario:

Weight   Grade   Weighted_Grade
10%      56        5.6
5%        0        0.0
10%       0        0.0
20%      40        8.0
15%      76       11.4
10%       0        0.0
30%      75       22.5
100%              47.5  < --- Final Weighted grade if missing work is counted

However the instructor decided to exclude missing grades.

Available   Redistributed          Weighted
Weight      Weight        Grade    Grade
10%         13%           56        7
20%         27%           40       11
15%         20%           76       15 
30%         40%           75       30
75%        100%                    63 < -- Final Weighted Grade, ignoring missing work

 

[jrmathes]

Thank you very much for explaining, I appreciate it! The grades are not missing, there are no grades to calculate for "no grade" column. Thanks again!

 

[jrmathes]

Thank you!! I was wondering how to do it when it doesn't equal 100% - thanks for taking the time to explain.