Dropping Lowest Grade

Welcome to the Forum!

How is your data organized? Do you have one column for the number of correct answers, and another for the corresponding value of points for that quiz?

When you say you want to drop the lowest scores, do you mean the number of correct answers, or the total value of the quiz? That is, a score of 5 on a 20-point quiz is worth 10 points and therefore higher than a score of 6 on a 10-point quiz worth 6 points? Or is it lower because 6 correct answers is better than 5?

(BTW I'm not quite understanding how you're using the formulas you showed to drop the 3 lowest scores.)

As is often the case, it would be very helpful if you attached a sample workbook. Fortunately this forum allows uploaded attachments, and is fairly generous about size limits.

Hi Guys!
Thanks for the quick response! I'll attach the workbook so you can get an idea of what I'm talking about!

So you can see the problem column is the "RATs" column on the far left. Some are worth 20, one is worth 19, and some are worth 10. I basically need to drop the three lowest "percentages", and then the remaining need to go into the 12% of the total grade. But when calculating how much of that 12% the student gets from RATs, I need the earned points/total points because the 10 point assignments contribute half as much to the grade as the 20 point assignments (if that makes sense). Hopefully with the spreadsheet it will make more sense... I find it's easier to explain in person and more difficult to explain with typing

@Beaunydal

Thanks for the formula and help, but I don't think it works exactly! If you remove the three lowest scores manually, you can see the difference. With the -SMALL function, it will remove the lowest number, and not necessarily the lowest percentage. So it removed the 4/10 instead of 5/20, and 4/10 is actually a higher percentage.

Here's a small update:

I figured out somewhat of a solution using the SUMIF function. Basically, I set up SUMIF(PERCENT_COLUMN,SMALL(PERCENT_COLUMN,1),GRADE_EARNED_COLUMN).

So it would look at the percent column, see a 0, go to the corresponding entry in the grade column, and add it for the first, second, and third smallest values. However, this seems to only work if each of the smallest values occurs once. So let's say I have a 3/10 and 3/10 as the first and second lowest grade. It'll give back 6/20, and 6/20 for the two entries. Hopefully that also makes sense... if anyone thinks they can figure this out (because they have significantly more excel experience than me, hopefully!), I would love to talk through an instant messaging program or something where we can communicate directly and quickly. Then I could post the solution up here (or you could) quickly!

I have noticed that when there are duplicate values of 'Score/Test Max' the RATs calculation can be different depending on which of the duplicate tests are omitted from the calculation.
The attached doc has 4 tests that all yield a minimum 'Score/Test Max' value of 0.5...
10/20, 10/20, 5/10 and 5/10.
When the tests omitted are 5/10, 5/10 and 10/20 the RATs value is smaller than when the omitted test are 10/20, 10/20 and 5/10.
For consistency I think that this effect should be considered.
The attached doc's first sheet shows how the RATs could be calculated manually by sorting the 'Scores', 'Test Max' and 'Scores/Test Max' columns along with SUM formulas that omit the rows containing the three worst results.
The other sheets show one way of doing the same with formulas.
The second sheet ignores the effect of the omitted tests' maximum possible mark and so would produce inconsistent results when there are duplicates of the minimum value of 'Scores/Test Max'.
The third sheet ensures that the RATs calculation is the greatest possible value when there are such duplicates.

I agree that helpers are certainly worthwhile here - you could (pending possibility of interspersed blanks etc) use single cell arrays but they would be complex & inefficient.

If you don't have to worry about interspersed blanks (ie every entry has a number and Test Max always exceeds 0) then in single cell array terms:

A23:
=SUM(IF(ISNA(MATCH(ROW($A$2:$A$18),ROUND(MOD(SMALL($A$2:$A$18*MAX($B$2:$B$18)/$B$2:$B$18+ROW($A$2:$A$18)/1000000,{1,2,3}),1)*1000000,0),0)),A$2:A$18))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied to B23