Posted on 11/22/2006 6:25:50 AM PST by Tanniker Smith
Advice needed from the Brain Trust:
I'm hoping some computer geeks better than I can find an answer to this Excel problem.
Basically, I've used Excel to calculate grades before, and that's fine. I usually have 3 exams and I drop the lowest score. Again, that's easy. However, this marking period, I have been informed that I should give a cumulative grade for the first and second marking periods. (It is math, after all, which builds upon itself.)
Now, to be fair to the students, I would like to drop their lowest two tests out of the six, not just the lowest of the first three and the lowest of the second three.
Is there an easy way of doing this that I can program over the holiday weekend?
TS
I'm no good at Excel or math. I'd suggest flunking them all. You'll get a reputation of being a tough teacher.
Make a spreadsheet set up with test dates as rows, student grades as columns.
Use data sort to sort all of the students scores top to bottom for each student. That way all of the lowest scores will be at one end of the column.
Sum the cells containing the lowest grades by student and apply your grade calculation formula.
I meant, sum the columns using a formula that omits the lowest grades (which should be the same rows of each column after you sort).
I was always taught that life is not fair.
If you decide to be fair, you can use the sumif statement; sum all grades >= median.
Are you considering how this will affect their self-esteem?
:-)
Sum them all and then subtract from that the lowest 2 using the SMALL function. A1:F1 is the range of the scores and -SMALL(A1:F1,1) subtracts the smallest and -SMALL(A1:F1,2) subtracts the next smallest.
=SUM(A1:F1)-SMALL(A1:F1,1)-SMALL(A1:F1,2)
LOL!!
I don't know the SMALL function. That looks simpler than what I would have done.
I would have used the MIN function to find the lowest score. Then I would find the second lowest by taking the MAX of the 6 values which are the minimums of all but the first, second, third, fourth, fifth, and six scores.
Very nice. I've never used the small function.
DING! DING! DING!! We have a winner!
Thank you. I didn't know about the SMALL function. That's exactly what I needed. I'll give it a try.
Been there.
Done that.
Bought the T-shirt.
... okay, I haven't bought the T-shirt, but I saw one in a catalogue that said, "If I'm talking, why aren't you talking notes?"
TS
Oh man, I wish one of my teachers had worn that shirt to class. I would have had newfound respect.
And probably taken notes.
Either that, or the one that says:
LOL!
Disclaimer: Opinions posted on Free Republic are those of the individual posters and do not necessarily represent the opinion of Free Republic or its management. All materials posted herein are protected by copyright law and the exemption for fair use of copyrighted works.