Grading Schemas (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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):

=SUM(LARGE(D8:I8,ROW($1:$5)))

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.

Reply | Quote

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,(J8-MIN(D8:I8))/(COUNT(D8:I8)-1),J8/COUNT(D8:I8))”

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

A requirement for passing the course might be a) a sufficient total score (where the lowest result is omitted) and 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.

Reply | Quote

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

Reply | Quote

Wow – did I write that?

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.

Reply | Quote

Hans,

Well, I may have contributed the “no quizzes” at the end if the count is not >0.

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

Reply | Quote