Chronology Current Month Current Thread Current Date
[Year List] [Month List (current year)] [Date Index] [Thread Index] [Thread Prev] [Thread Next] [Date Prev] [Date Next]

Re: Grading & EXcel

Richard Bowman's solution below IS the best I've seen--has Excel always had
the 'SMALL' function or is that new? Is there an equivalent in Quattro (I'm
a fairly recent convert from WP/QPRO to Word/Excel and searched rather
extensively for such a function in QPRO (versions 6 and earlier)?

The only downside to the technique is how to enter ABSENCES or EXCUSED
ABSENCES. If not entered numerically as '0' (zero), these will simply be
ignored and the SMALL function will grab the lowest numerical value. The
advantage of my earlier technique of turning the low scores into text is
that one can use 'abs' or 'exc'
in the field as well--will sum as zero.

Rick

-----Original Message-----
From: Richard L. Bowman <rbowman@BRIDGEWATER.EDU>
To: phys-l@atlantis.uwf.edu <phys-l@atlantis.uwf.edu>
Date: Sunday, December 14, 1997 8:06 AM
Subject: Re: Grading & EXcel


At 04:02 PM 12/12/1997 -0700, Dan MacIsaac wrote:
Req for Excel expert assistance:

I usually grade the best 15 of 20 activities; best 2 of 3 midterms; best
13 of 14 labs etc to avoid giving makeups. Problem is, at the end of
semester
I have to then manually pick these 'best' grades out of the records.

Is there an Excel wiz out there who knows how to write Excel functions??
I'd
like to acquire or get assistance with producing a BestNof function. This
function would allow me to set a cell range and an integer. The function
should return the sum of the best N scores of the selected cells (the
sum of the highest N values in the range of cells).

Anyone know how to do this or know someone who knows how to do this?

Dan,

Here is a way to exclude the two lowest scores of a series in columns B
through F. I think you can modify it fairly straightforwardly for each of
your situations.

=(SUM(B3:F3)-SMALL(B3:F3,1)-SMALL(B3:F3,2))/(COUNT(B3:F3)-2)

It is given in a tutorial on grades and Excel that I have written and
posted at:

http://www.bridgewater.edu/cescc/acadcomp/ac.html

Hope this helps.

Richard