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

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

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

4. ## 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,(J8-MIN(D8:I8))/(COUNT(D8:I8)-1),J8/COUNT(D8:I8))"

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

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

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

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•