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:

	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>

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