Hi,

I shouldn't have said "average" in the subject line, I guess, because your
formula drops the lowest three average scores. As I explained in the last
post, I want to drop the scores which improve the total achieved/total
possible after dropping.

But thanks for the formula. I might be able to play with SUM and IF and
adapt it to my policy.

--Matt


On 6/3/05 12:53 PM, in article , "N
Harkawat" wrote:

Matthew

Assuming that you want to drop the 3 smallest % scores that are most
detrimental to the overall average I came up with the following formula.
based on this example below in the range A1:A8

Numerator Denominator
10 10
15 20
800 1100
30 30
40 45
50 60


=SUM(IF((A2:A7/B2:B7)MAX(SMALL((A2:A7/B2:B7),{1,2,3})),A2:A7))/SUM(IF((A2:A7/
B2:B7)MAX(SMALL((A2:A7/B2:B7),{1,2,3})),B2:B7))

Array entered (ctrl+shift+enter)
gives me a score of 94.11%

In the above example the scores 2nd,3rd and 6th were dropped since they
were they were the smallest 3 % of the total

You can adopt it to fit your range accordingly.

Hope it helps...


"Matthew Leingang" wrote in message
...
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