Excel Question - Grading Edition

[Izulde]

So I use Excel for my gradebooks, but tabulating grades by hand is a chore, and I'd like to figure out a way to auto-calculate the final grades.

I use a combination of letter grades and weighting.

So for example, say Student A has the following grades:

Assignment 1: B
Assignment 2: D
Assignment 3: B
Final Exam: C+
Research Paper: C+
Presentation: A
Revision Workshops: A
Department Exam: C-

and the weights are:

Assignment 1: 5%
Assignment 2: 10%
Assignment 3: 15%
Final Exam: 10%
Research Paper: 35%
Presentation: 10%
Revision Workshops: 10%
Department Exam: 5%

How would I create a formula that tabulates this?

[JonInMiddleGA]

Wouldn't you need point values for each of the letters?
(Presumably those exist ... right?)

I'll pretend that A = 4, B=3, C=2, D=1 ... and pretend that there aren't any +/- in the sample grades (since I don't know the numerical equivalents that would apply)


At that point, brute force probably works

The maximum number of "points" available here would be 4 TOTAL (for all A's)

So ... (.05x3) + (.10x1) + (.15x3) + (.10x2) + (.35x2) + (.10x4) + (.10x4) + (.05x2) = 2.5 (out of a possible 4)

Whatever that 2.5 works out to should be the semester grade (I think)

edit to add: That passes my quick eyeball test, since that's about a C+ and that's the grade given on 45% of the weighted assignments

double edit: There's probably a more elegant way of doing it -- and obviously I used the values but you would insert Cells in the formula so it could be filled downward in the final grade column of the Excel sheet -- but in the absence of elegance, brute force like that usually ends up working okay.

[Izulde]

A = 95
A- = 90
B+ = 88
B = 85
B- = 80
etc.

[JonInMiddleGA]

Okay, let's see here then

(.05x85) + (.10x65) + (.15x85) + (.10x78) + (.35x78) + (.10x95) + (.10x95) + (.05x70) = 81.1, so a B- for the semester

Looking at the grade distribution that actually feels a little high to me.

And someone better than me can come along & tell you a whole IF= thing that would convert letter grades into numerical values automatically (I'd be the guy who just recorded them as numerical values from the get-go tbh)

[Izulde]

That actually would make sense (FWIW it's a B... 81-87 range is a B final grade ), given that the As in presentations and workshops are providing a major boost.

[JonInMiddleGA]

It still feels funny to me though ... 60% of his grades (by weight) are lower than that 81.1 I ended up with.

Maybe the ones that were higher are just "more higher" enough to make it work out that way ... but at 2am local time, I think the math is beyond me.

[stevew]

Seems like you'd save yourself a lot of pain by just assigning points/total points for everything if possible. So if he got a C+ on the paper it'd be a 27/35

[rowech]

I would just assign ponts as it would make it a lot easier. That said, if you want to do this way, I would make column A the name of the assignment, column B would be a column with a formula to lookup the value of the grade from a small chart somewhere else in the file, column C would be the weight, Column D would be column B times column C, and then you would need a cell somewhere to give the final grade overall by summing all of your column C entries.

The small chart somewhere else would award points based on the letter grade, 13 = A+, 12 = A, 11 = A-, etc. down to the F = 1, and then a 0 for incompletes. (I).

In column B, I would then have to use the VLOOKUP feature to lookup each grade on the chart to get the right points in there for each letter grade.

You might need to rearrange the cells/columns/rows to make it look better, but this would get the job done.

[henry296]

If you have the weights you can use a sumproduct function.

[SteveMax58]

Originally Posted by henry296

If you have the weights you can use a sumproduct function.

Yeah I agree.

Do you not do actual scores (like 82 or 97)? Assuming you have them...you can do this easily and even manipulate the weights until you get the right weighting factor.

1. Make row1 your "weight factor" row. You'll put the weight factors here above each column header so you can manipulate them later if you want to make a small change without having to go back & edit every formula.

2. Make row2 your "column" header row. This is where you'll plug the titles of your columns (e.g. students, Assignment 1, Assignment 2, Final Exam, etc.)

3. So column A is your students(titled in cell A2, with A3-Axxx your students). Enter your students' grades across each row for each assignment/work.

4. If you used only the columns you mentioned earlier, you will have data in columns B thru I. So your total grade formula in J3 should look like....
=SUMPRODUCT(B$1:I$1;B3:I3)

5. Drag that formula down for as many students as you have.

[SteveMax58]

Here's what mine looks like if its of help. Notice I just christmas treed the 2nd and 3rd row scores.

	0.05	0.10	0.15	0.10	0.35	0.10	0.10	0.05
Students	Assignment 1	Assignment 2	Assignment 3	Final Exam	Research Paper	Presentation	Revision Workshops	Department Exam	Total
Johnny	80	50	75	70	58	85	65	20	63.55
Billy Ray	81	51	76	71	59	86	66	21	64.55
Michael Sue	82	52	77	72	60	87	67	22	65.55

[JPhillips]

Just use eClass or Moodle. It does that shit for you!

[Izulde]

Thanks, guys. Definitely stuff I'll keep in mind for next semester. Interestingly enough, I found that Google retains calculations when put in the search engine, so for this semester, I can just swap out the numbers of the A x .05 + B X .10 + C X.15, etc. formula.

JPhillips - Online gradebook systems are hit and miss. The only one I've used at the schools I've taught that wasn't a dreadful mess was Canvas. It's also not an option to have without a lot of hassle on one campus I teach.