Calculating the weighted average of letter grades

[Megefl]

Hi all,

I'm something of an Excel neophyte, and while I've mostly been able to cobble together some clunky algorithms to do what I need, I'm stuck on one point which has a few more variables than I can sort through, and have come up dry after scouring the internet.

Basically, I'm preparing a grading spreadsheet into which my TAs can enter student grades for each individual student and each assignment.
That looks a bit like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Midterm[/TD]
[TD]Paper[/TD]
[TD]Final Exam[/TD]
[TD]Course Average[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD]A[/TD]
[TD]B+[/TD]
[TD]A-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD]A-[/TD]
[TD]B[/TD]
[TD]B+[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to find out how to get them a weighted average letter grade for the final column.

For each of 7 assignments, students receive letter grades with +s or -s.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A-[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B+[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B+[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B-[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C+[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]C-[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]D+[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

Each assignment is weighted differently:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Assignment[/TD]
[TD]Weight factor[/TD]
[/TR]
[TR]
[TD]Midterm 1[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]Midterm 2[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]Paper[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]Participation[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD]Final 1[/TD]
[TD="align: right"]11.67%[/TD]
[/TR]
[TR]
[TD]Final 2[/TD]
[TD="align: right"]11.67%[/TD]
[/TR]
[TR]
[TD]Final 3[/TD]
[TD="align: right"]11.67%[/TD]
[/TR]
</tbody>[/TABLE]


Because the weight factors and number of grade values are a bit complicated, I'd love it if Excel could average this for them.
Any insight into how I can make this happen would be hugely appreciated.

 

[Fennek]

Hi,

as a first step this might help:

function AvGrade(rng as range) as double
for each r in rng
select case r.value
case is "A" : Av = Av + 1
case is "A-" : Av = Av +2

etc

end select
AvGrade = Av / 3
end function

regards

 

[bimatmpmet]

Hi all,

I'm something of an Excel neophyte, and while I've mostly been able to cobble together some clunky algorithms to do what I need, I'm stuck on one point which has a few more variables than I can sort through, and have come up dry after scouring the internet.

Basically, I'm preparing a grading spreadsheet into which my TAs can enter student grades for each individual student and each assignment.
That looks a bit like this:

	Midterm	Paper	Final Exam	Course Average
Student 1	A	B+	A-
Student 2	A-	B	B+

<tbody>
</tbody>

I want to find out how to get them a weighted average letter grade for the final column.

For each of 7 assignments, students receive letter grades with +s or -s.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

A	1
A-	2
B+	3
B+	4
B-	5
C+	6
C	7
C-	8
D+	9
D	10
F	11

<tbody>
</tbody>

Each assignment is weighted differently:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Assignment	Weight factor
Midterm 1	10%
Midterm 2	10%
Paper	25%
Participation	20%
Final 1	11.67%
Final 2	11.67%
Final 3	11.67%

<tbody>
</tbody>

https://gwacalculator.ph/
Because the weight factors and number of grade values are a bit complicated, I'd love it if Excel could average this for them.
Any insight into how I can make this happen would be hugely appreciated.

Hi,

I am working on an evaluation sheet in excel 2010. Which looks as following:

Criteria Weighfactor Score (A/B/C/D) Weighted score
1.Housekeeping 40% A ...
2.QHSE 20%
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...

Every criteria has a weight factor which is a number. The rating that the evaluator is giving the criteria is a letter grade.
I do have the formula to calculate the average of all letters which is

=CHAR(INT(SUM(IF(E25:E78<>"",CODE(E25:E78))/COUNTA(E25:E78))))

Does anybody have an idea how to implement the weight factor (for example 40%) to the letter grade so at the end at the right average is calculated.

Thanks for your help,

 

[StephenCrump]

@bimatmpmet

It would have been better to start a new thread. Many helpers on the Forum search for unanswered threads, and so may have missed your question.

Your question is not totally clear, but does this help?

	A	B	C	D	E	F
1	Weights
2	40	30	10	20
3
4	Criteria				Weighted	Weighted
5	(1)	(2)	(3)	(4)	Code	Char
6	D	C		C	67.4	C
7	A	D	D	D	66.8	B
8
9	A	C	D	D	66.5	B
10	A	C	C	D	66.4	B
11	B			D	66.7	B
12	C	C	C	D	67.2	C
13	D	D		C	67.8	C
14	C	D	B	D	67.4	C
15	C	B	C	D	66.9	B
16
17
18	Average				Weighted
19	B	C	C	C	C	B
Sheet1

Cell Formulas
Range	Formula
E6:E15	E6	=IF(COUNTA(A6:D6),SUMPRODUCT(IF(LEN(A6:D6),CODE(A6:D6)),A$2:D$2)/SUMPRODUCT(A$2:D$2,LEN(A6:D6)),"")
F6:F15	F6	=IF(E6="","",CHAR(E6))
A19:D19,F19	A19	=CHAR(AVERAGE(IF(A6:A15<>"",CODE(A6:A15))))
E19	E19	=CHAR(AVERAGE(E6:E15))

I have followed your method of averaging. In column D this gives an average grade of C for criterion 4, even though 9 out 10 grades are D. This is because the average CODE for the column is 67.9, i.e. closest to 68 which is D. But CHAR(67.9) is the same as CHAR(67) which is C. Perhaps you could consider rounding to the nearest integer?

For similar reasons, the weighted grades in E19 and F19 may differ, depending on whether or not you convert the weighted CODE into a CHAR for each line.

 

[Fluff]

@bimatmpmet

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculating the weighted average of letter grades
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.