• July 23, 2016

# Chronicle Forums

July 23, 2016, 7:12:35 am
 Welcome, Guest. Please Log In to participate in forums.
 News: Talk online about your experiences as an adjunct, visiting assistant professor, postdoc, or other contract faculty member.
 Pages: 1 [2]
 Author Topic: Excel gradebook that calculates current grade  (Read 10124 times)
blackbart
After lurking for eons, finally a
Member

Posts: 103

Amazed I'm paid for what I do.

 « Reply #15 on: May 02, 2012, 12:20:14 pm »

Here's a sample solution from one of my classes. It's a single formula (I had to hit "enter" a couple of times to get it all to show up here) that generates a running percentage average based on which grading opportunities have scores recorded. The formula ignores "missing" scores.

In this case, values in C3:I3 are assignment grades, the average of which counts for 35% of the course grade; values in J3:M3 are exam grades, the average of which counts for 50% of the course grade; and the value in N3 is the final exam grade, which counts for 15% of the course grade. (I just copied this from the student in row 3; obviously the row reference would change for different students.)

=0.35*IF(COUNTBLANK(C3:I3)=COLUMNS(C3:I3),0,AVERAGE(C3:I3))+0.5*IF(COUNTBLANK(J3:M3)
=COLUMNS(J3:M3),0,AVERAGE(J3:M3))+0.15*IF(COUNTBLANK(N3)=1,0,N3))/(IF(COUNTBLANK(C3:I3)
=COLUMNS(C3:I3),0,0.35)+IF(COUNTBLANK(J3:M3)=COLUMNS(J3:M3),0,0.5)+IF(COUNTBLANK(N3)=1,0,0.15))
 « Last Edit: May 02, 2012, 12:21:35 pm by blackbart » Logged

"The more the words, the less the meaning, and how does that profit anyone?"
pb_ft
New member

Posts: 43

 « Reply #16 on: May 03, 2012, 1:23:46 pm »

Here are some other Excel functions I have found useful in determining grades.

Use SMALL to drop multiple grades as MIN only determines smallest.
=SMALL(cell1:cell5,1) lowest
=SMALL(cell1:cell5,2) 2nd lowest

LOOKUP can be used to determine letter grade as outlined below.
=LOOKUP(cell with grade,{0,60,65,70,73,77,80,83,87,90,93},{"F","D","D+","C-","C","C+","B-","B","B+","A-","A"})

The numbers above are whole numbers due to the slightly different way I determine the final grade.
 Logged
polly_mer
practice makes perfect
Distinguished Senior Member

Posts: 27,305

Have you worked on that project today?

 « Reply #17 on: May 03, 2012, 4:51:51 pm »

Oooh, I didn't know about SMALL or LOOKUP.  Thanks!
 Logged

I've joined a bizarre cult called JordanCanonicalForm's Witnesses.  I have to go from door to door asking people things like, "Good evening, sir!  Do you have a moment to chat about Linear Transformations?"
bioteacher
chocolate loving
Distinguished Senior Member

Posts: 7,509

Confused and sad. Or happy. I'm not sure...

 « Reply #18 on: May 03, 2012, 4:53:21 pm »

I love VLookup to move info from one sheet to another and keep it assigned to the correct student.
 Logged

Lifesaving dream crusher and member of the Real vs. Zeal club.
tee_bee
I've really made it in academe, now that I am a
Distinguished Senior Member

Posts: 4,643

 « Reply #19 on: May 04, 2012, 4:18:47 pm »

Here are some other Excel functions I have found useful in determining grades.

Use SMALL to drop multiple grades as MIN only determines smallest.
=SMALL(cell1:cell5,1) lowest
=SMALL(cell1:cell5,2) 2nd lowest

LOOKUP can be used to determine letter grade as outlined below.
=LOOKUP(cell with grade,{0,60,65,70,73,77,80,83,87,90,93},{"F","D","D+","C-","C","C+","B-","B","B+","A-","A"})

The numbers above are whole numbers due to the slightly different way I determine the final grade.

One of my favorite Excel function is the =VLOOKUP function, that works similarly. This is intriguing, though.
 Logged
 Pages: 1 [2]
Jump to:

• 1255 Twenty-Third St., N.W.
• Washington, D.C. 20037

Get the insight you need for success in academe.