Totaling grades and disregarding the lowest score

[Mattantaliss]

Hello,

I'm putting together a grade sheet for the upcoming semester, and in the past we have calculated homework totals by taking the twelve scores for the assignments and disregarding the lowest two (so we total the ten highest). As an example, if the twelve scores were stored in cells A1 to L1, I have calculated this before as:

=SUM(A1:L1)-SMALL(A1:L1,1)-SMALL(A1:L1,2)

Now the tricky part. This time around, each homework assignment will have two components, and so I have twenty-four cells storing each of the two component scores for each of the twelve assignments. Is there a slick way to total all of these scores up and then throw out the two lowest homework (paired-component total) scores?

Thanks,
Matt

 

[Aladin Akyurek]

Aladin Akyurek - If you consider the totals for each homework assignment (which each consist of a W component and an A component), you have

HW1 = W1 + A1 = 4
HW 2 = W2 + A2 = 3
HW3 = ... = 5
HW4 = 4

So 3 and 4 (one of them) are the two lowest scores, which leaves 5 + 4 = 9 for the total homework points.

Control+shift+enter, not just enter:

=SUM(LARGE(IF(MOD(COLUMN($B$1:$H$1)-COLUMN($B$1),2)=0,B2:H2+C2:I2,""),
   ROW(INDIRECT("1:"&(COLUMNS($B$1:$I$1)/2)-2))))

 

[Mattantaliss]

Aladin Akyurek - Looks like that is the formula I've been after. Thank you.