• August 25, 2016

Chronicle Forums

August 25, 2016, 2:09:37 am
 Pages: 1 [2]
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.

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

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
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.