Chemical Education Today
Letters Spreedsheet Grade Books Two notes concerning the use of Microsoft Excel as a grade book have appeared recently (1, 2). Both have addressed ways to drop the lowest two quiz grades in calculating a mark. There is actually a function available within Excel that is exceedingly simple to use and is completely flexible—you can use it to drop as many marks as you like. It is the function SMALL, which returns the kth smallest value in a data set, and is described in the Excel Function Reference. The following expression provides an example of how one would use SMALL to drop the two lowest grades from a list of nine marks: =SUM(AH2:AP2) { SMALL(AH2:AP2,1) { SMALL(AH2:AP2,2) I have entered nine marks for a student in cells AH2 through AP2; the entry above appears in the cell I use to calculate the sum of the seven highest marks for this student. Literature Cited 1. Moseley, C. G. J. Chem. Educ. 1996, 73, 62. 2. Billo, E.; Joseph, J. J. Chem. Educ. 1993, 70, 148.
Jonathan Mitschele Saint Joseph’s College Standish, ME 04084
Moseley replies: In response to notes by J. E. Billo (1) and by me (2) presenting ways to drop the two lowest quiz grades from a list in computing student quiz totals, Mitschele has suggested that there is a simpler method than presented in these notes. His comments are basically correct, and I should have probably mentioned this approach in my note; however, my goal (which was not stated as clearly as it should have been) was to develop a procedure which would give both a correct running quiz total at any time during the quarter for any number of quizzes taken (up to seven) and also the total of the best five quiz grades for the overall quiz score. This requires that the procedure total the quiz grades for the first five quizzes taken, drop the one lowest grade for six quizzes taken, and drop the two lowest grades for seven quizzes taken. My procedure has the flexibility to follow these steps, but if I understand Mitschele’s procedure correctly, his does not. Mitschele’s procedure could be easily modified with two IF functions to allow it to calculate the correct quiz total when fewer than the maximum number of quizzes has been taken. Assume that the seven quiz grades for
144
a student are entered in cells C2 to I2 and that a blank cell (Z2) is available. The two IF functions below would allow his procedure both to give the correct running quiz total for any number of quizzes taken (up to the seven) and to total the best five quiz grades for the overall quiz score: For the QuizT Cell: =IF(COUNT(C2:I2)=7,SUM(C2:I2) – SMALL(C2:I2,1) – SMALL(C2:I2,2)Z2) For the Z2 Cell: =IF(COUNT(C2:I2)=6,SUM(C2:I2) – SMALL(C2:I2,1),SUM(C2:I2) With this addition, Mitschele’s procedure is as flexible as the one I proposed and is somewhat simpler to set up and modify for the various numbers of quizzes given and/or dropped in different courses. Literature Cited 1. Billo, E.; Joseph, J. J. Chem. Educ. 1993, 70, 148. 2. Moseley, C. G. J. Chem. Educ. 1996, 73, 62.
Charles Moseley Ohio State University Lima, OH 45804 Billo replies: The Sort macro that I described (J. Chem. Educ. 1993, 70, 148) is not limited to dropping the lowest two grades. The macro merely orders each student’s N quiz grades in descending order, so that the user of the spreadsheet can conveniently sum the highest M grades. In my version of record-keeping, I use a zero to indicate an actual score on a quiz, and a blank to indicate an absence. I find this to be a useful distinction. The macro approach arranges the scores in descending order, so that when the lowest grades are “lopped off”, it doesn’t matter whether they are zeros or absences. The SMALL function, on the other hand, is like the AVERAGE function: it considers only the cells containing entries. It finds the smallest value(s) in the cell containing entries, and ignores the blank cells. Thus the above approach, if applied to my records, would not give the desired result. But certainly, for users who adopt a different system, using the SMALL function may provide a compact and convenient way to handle the “best M out of N” quiz grade problem. E. Joseph Billo Boston College Chestnut Hill, MA 02167
Journal of Chemical Education • Vol. 74 No. 2 February 1997
