Totaling grades and disregarding the lowest score

Mattantaliss

Board Regular
Joined
Sep 6, 2005
Messages
52
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
Probably there is. How is the data formatted? Are there headers, and if so, what are they?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
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

New Member
Joined
Jul 17, 2009
Messages
46
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
 

Mattantaliss

Board Regular
Joined
Sep 6, 2005
Messages
52
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

Board Regular
Joined
Sep 6, 2005
Messages
52
Aladin Akyurek - Dropping the two lowest (total) homework scores would give a desired result of 9.
 

Mattantaliss

Board Regular
Joined
Sep 6, 2005
Messages
52
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top