• October 30, 2014
October 30, 2014, 7:39:43 PM *
Welcome, Guest. Please login or register.

Login with your Chronicle username and password
News: Talk about how to cope with chronic illness, disability, and other health issues in the academic workplace.
 
Pages: 1 [2]
  Print  
Author Topic: Excel gradebook that calculates current grade  (Read 9941 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: 46


« 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: 37,441

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,410

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,556


« 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]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.9 | SMF © 2006-2008, Simple Machines LLC Valid XHTML 1.0! Valid CSS!
  • 1255 Twenty-Third St., N.W.
  • Washington, D.C. 20037
subscribe today

Get the insight you need for success in academe.