Results 1 to 10 of 10
Thread: Grading Schemas (Excel 2003)

20071112, 18:57 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Gradong Schemas (Excel 2003)
Problem9
Would this do what you want: In j8, and copy down as needed, enter "=sum(d8:i8)min(d8:i8)", withouit the quotation marks, of course.

20071112, 18:58 #2
 Join Date
 Jul 2007
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Grading Schemas (Excel 2003)
I am trying to figure some "grading" schemas for my Business Ethics class. I have an Excel file to which I have been posting each Case Study grade for the students fBusiness Ethics. However, when the semester is complete, only the highest 5 of 6 grades will be used in determining that portion (40%) of their final grade. So, attached is the grading schema that I am using (without student names for confidentiality) and wanted to get your thoughts as to how I might set up a calculation that would make it easy to take the top 5 of 6 grades without having to do a lot of manual shifting around. (I am not even sure if it is possible, so let me know if I have just asked the "impossible" or "too complicated.")
See attachment.

20071112, 19:01 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Grading Schemas (Excel 2003)
If all students complete 6 assignments, Jeff Kirk's solution is just the thing.
If some might complete only 5 assignments, you can use this array formula (confirm with Shift+Ctrl+Enter):
<code>
=SUM(LARGE(D8:I8,ROW($1:$5)))
</code>
This will add the 5 highest scores, whether there are 5 or 6 scores. If there are 4 results or less, the formula will return an error.

20071112, 19:13 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Grading Schemas (Excel 2003)
This might work in cell k8, copied down. It assumes that if a student completes less than 6 tests, all of the tests will be considered in his final average.
"=IF(COUNT(D8:I8)>5,(J8MIN(D8:I8))/(COUNT(D8:I8)1),J8/COUNT(D8:I8))"

20071112, 19:18 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Grading Schemas (Excel 2003)
OK, but the student might not get a final grade if (s)he doesn't have 5 results to average. The original poster can decide which solution best fits the situation.

20071112, 19:33 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Grading Schemas (Excel 2003)
Hans,
Not sure what you mean that if the student doesn't have 5 test scores he might not get a final grade. Just curious.

20071112, 19:51 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Grading Schemas (Excel 2003)
A requirement for passing the course might be a) a sufficient total score (where the lowest result is omitted) and [img]/forums/images/smilies/cool.gif[/img] having completed at least 5 assignments. If the student has completed only 4 assignments, he/she cannot pass yet, even if the average of those four is high enough.

20071113, 14:56 #8
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Grading Schemas (Excel 2003)
Hans,
About 2 years ago, you wrote the following formula for me, which, I confess, I've never been able to understand:
{=IF(COUNT(B13:J13)>0, SUM(LARGE(B13:J13,ROW(INDIRECT("1:"&MIN(COUNT(B13: J13),MAX(drop_quizzes,COUNT(B13:J13)drop_quizzes)))))) / MIN(COUNT(B13:J13),MAX(drop_quizzes,COUNT(B13:J13)drop_quizzes)), "no quizzes")}
In this case, B:J is the range where quiz scores are kept, row 13 is just 1 row of many where student grades are kept with 1 row per student, and drop_quizzes is the name of a cell that has how many quizzes will be dropped when computing the quiz average.
Would this be useful here?
The nice thing about the formula is that it drops quiz scores, up to the number given by drop_quizzes, only when the number of scores exceeds drop_quizzes. For example, if drop_quizzes is 2, the student's quiz average will include all quizzes when only 2 quizzes have been taken, drop the lowest when the number taken is 3, and drop the 2 lowest when the number of scores is 4 or more.
Fred

20071113, 15:27 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Grading Schemas (Excel 2003)
Wow  did I write that? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
In the workbook the original poster attached, there are only 6 assignments, so this formula wouldn't be necessary. But if the number of assignments is flexible, it could come in handy.

20071114, 15:55 #10
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Grading Schemas (Excel 2003)
Hans,
Well, I may have contributed the "no quizzes" at the end if the count is not >0. <img src=/S/clown.gif border=0 alt=clown width=15 height=15>
Actually, you may have done the formula for my lab assignments, of which there were 24 and I wanted to drop the lowest 2 also (uses a named cell called, guess  drop_labs). Maybe I adapted it to the quizzes of which there are no more than 9 and probably more like 7.
In any case, I still can't understand it and don't even try.
Fred