Hello,

First, let me say that I have a solution to this problem but I am looking
for a better one. Second, I apologize if this gets a little long.

I keep scores for homework assignments in a spreadsheet, recording not the
percentage but the numerator (points achieved) and the denominator (points
possible). A student's homework score is computed by summing all the
numerators and dividing by the sum of all the denominators. This way a
perfect score on a 50-point problem set improves your homework score more
than a perfect score on a 10-point problem set would.

But if only it were that easy! We have a policy of "dropping" the n lowest
problem sets, where n is usually 2 or 3. This is to give the students some
slack. My colleagues and I have tried various interpretations of
"lowest"--lowest by percentage, lowest by z-score relative to the rest of
the class; and other hacks. I've finally decided that the "lowest" score is
the one that improves your total score the most if you neglect it. That is,
for each problem set, sum the numerators skipping this one, sum the
denominators skipping this one, and compare that to the original homework
score. I'm trying to find the best way to do this in Excel.

Let's suppose the numerators are in A1:Z1 and the denominators are in A2:Z2.
Then the change in homework score by dropping the problem set in column G
(say) is (SUM(A1:Z1) - G1)/(SUM(A2:Z2) - G2). I put these changes in
AA1:AZ1. Then in BA1 I have IF(RANK(AA1,$AA1:$AZ1)<=3,0,1), and so on down
to BZ1. I'm sure I don't have to put these in their own cells, but it helps
with debugging and conditionally formatting the original scores.

The adjusted numerator is then SUMPRODUCT(A1:Z1,BA1:BZ1), and the adjusted
denominator is SUMPRODUCT(A2:Z2,BA1:BZ1). Then I have to hide 52 columns of
intermediate values. :-)

It seems like there ought to be a one-cell formula to compute the adjusted
numerator. I'm not that good with array functions, though. I tried
something like

SUMPRODUCT(A1:Z1,IF(RANK((SUM(A1:Z1)-A1:Z1)/(SUM(A2:Z2)-A2:Z2),
(SUM(A1:Z1)-A1:Z1)/(SUM(AZ:Z2)-A2:Z2))<=3,0,1)

But this gives a non-descriptive error (not to mention the fact that the
first and second arguments are identical!).

Additional complications:

* I need to break ties so exactly three are dropped. I do this by
adding to the change cell (SUM(A1:Z1) - G1)/(SUM(A2:Z2) - G2) a small number
times the column number. That's klunky but it works.

* I sometimes need to override policy and force a problem set to be
dropped. Currently my IF(RANK(AA1,$AA1:$AZ1)<=3,0,1) formula also
short-circuits to 0 if AA1 has the string "DROP" in it. Can I instead look
for a comment attached to the cell?

Thanks for any answers, advice, or clues.

--Matthew Leingang


--
Matthew Leingang
Remove caps for correct email address