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

 

[ChrisOswald]

Probably there is. How is the data formatted? Are there headers, and if so, what are they?

 

[AlphaFrog]

This will sum the 10 highest scores in A1:A12 plus the ten corresponding scores in B1:B12

=SUMIF(A1:A12,">"&SMALL(A1:A12,2))+SUMIF(A1:A12,">"&SMALL(A1:A12,2),B1:B12)

 

[mojo249]

Matt

I think the easiest way is to sort the sheet by student name, add subtotals and then apply your formula to the subtotalled rows.

Dion

 

[shemayisroel]

Hi Matt,

Could you post some sample data along with your expected result(s).

Try excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

 

[Mattantaliss]

Hi all,

Sorry for the delay in my response; the process of cleaning and moving took a lot more time out of my day than I had anticipated.

ChrisOswald/shemayisroel - I'm on a Mac (running Office 2008 for Mac), so I can't use excel jeanie to post a sample spreadsheet; but to give some idea of what I'm working with, say I have student names in column A, and four homework scores in columns B:I. An example would be:

Headers:
A1 - Student Name
B1:I1 - W1 A1 W2 A2 W3 A3 W4 A4

Data:
A2 - John Doe
B2:I2 - 1 3 2 1 1 4 3 1

So the homework score totals (HW1, HW2, HW3, HW4) would be 4, 3, 5, and 4 and what I'm looking for is a formula to stick in J2 that would give me 9 as an answer (totaling those four subtotals and dropping the lowest two).

AlphaFrog - I don't think that is quite what I'm after, only because the lowest score of the first component of a homework may not necessarily be associated with the lowest total homework score.

mojo249 - Yeah, it may be that I just need to store homework totals in addition to the component scores and work with those totals instead of dealing with an ugly formula, but if there is a clever way to obtain the desired sum, it would help keep things clean.

Thanks,
Matt

 

[Aladin Akyurek]

Given A1:I2...

<TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Student</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2417811 class=xl65 width=64>W1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>W2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>W3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>W4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>John Doe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR></TBODY></TABLE>

What is the desired result for John Doe?

 

[Mattantaliss]

Aladin Akyurek - Dropping the two lowest (total) homework scores would give a desired result of 9.

 

[Aladin Akyurek]

Aladin Akyurek - Dropping the two lowest (total) homework scores would give a desired result of 9.

How did you get that 9, given the exhibit?

 

[Mattantaliss]

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.