Excel: Help give my students higher grades.

December 19, 2005 10:44 AM

Excel Wizards (or Apprentices, Probably) Grading is upon me again and there's something I've always wanted to do in Excel automatically -- from a range of values, drop the lowest N values and average the rest.

In my course I have a policy of dropping the lowest two quiz grades. So I'd like to average the nine highest quizzes out of the 11 quizzes I have recorded. Say the quizzes are a1:a11 so the average without dropping any is AVERAGE(A1:A11).

posted by ontic to computers & internet (7 comments total)

Perhaps you can sort the rows and then average (A3:A11)? I'm not sure how to automate that, though.
posted by JMOZ at 10:59 AM on December 19, 2005

This is a bad hack way of doing it within the cell, but:
=(SUM(A1:A11)-SMALL(A1:A11,1)-SMALL(A1:A11,2))/(COUNT(A1:A11)-2)
posted by milkrate at 11:02 AM on December 19, 2005

That's not a bad hack way, that's pretty much the right way.
posted by kindall at 11:06 AM on December 19, 2005

Thanks, you all just saved me better than an hour every semester for the rest of my life. That means I will have an extra day that I have lived after 12 years. And all this because you took the time to scribble down formulae on a website.
posted by ontic at 12:58 PM on December 19, 2005

askMeFi: Saving your life, one hour at a time...
posted by blue_beetle at 1:07 PM on December 19, 2005

I think milkrate means that it's a bad hack in that it isn't dynamic with regards to dropping j number of low scores. You have to include a SMALL([range], x) for 1..j and make sure you use the proper value of j over in COUNT([range], j)

In the interest of learning how to do user defined functions, I wrote this:
Function AverageButDropLowestN(Values, N)
Total = Application.WorksheetFunction.Sum(Values)
For DropMe = 1 To N
Total = Total - Application.WorksheetFunction.Small(Values, DropMe)
Next DropMe
AverageButDropLowestN = Total / (Application.WorksheetFunction.Count(Values) - N)
End Function

You can go to Tools>Macros>Visual Basic Editor and use Insert>Module and paste that in. Press Alt-F11 to go back to your worksheet and you'll have it in your worksheet to use thusly:

=AverageButDropLowestN(A1:A11,2)
posted by phearlez at 1:08 PM on December 19, 2005

Just as an aside: dropping the lowest 2 seems like a statistically bad thing to do, to me. You're making yourself look better though, to be sure. :)
posted by antifuse at 4:03 AM on December 20, 2005