software

hello

I've been struggling with a formula and could use some help.
Column A shows StudentNames and column B shows Grades. The columns are
sorted by A (ascend) then B (descend). I need a formula dragged down
column C that shows the SUM of the TOP 3 grades of each student in the top
row for that student.

nb: not every student has 3 grades. For these, I'd like to sum whatever
grades they have.

The result should look like this:

A B C

1 Bill 67 167
2 Bill 50
3 Bill 50
4 Bill 46
5 Ed 85 160
6 Ed 75
7 Sue 43 43
8 Tom ...etc

drowning in formulas...
Dorredorre wrote...
gt;I've been struggling with a formula and could use some help.
gt;Column A shows StudentNames and column B shows Grades. The columns are
gt;sorted by A (ascend) then B (descend). I need a formula dragged down
gt;column C that shows the SUM of the TOP 3 grades of each student in the top
gt;row for that student.
gt;
gt;nb: not every student has 3 grades. For these, I'd like to sum whatever
gt;grades they have.
gt;
gt;The result should look like this:
gt;
gt; A B C
gt;1 Bill 67 167
gt;2 Bill 50
gt;3 Bill 50
gt;4 Bill 46
gt;5 Ed 85 160
gt;6 Ed 75
gt;7 Sue 43 43
gt;8 Tom ...etc
....

The easiest way to do this, though it's somewhat inefficient, requires
a different formula in the top row than in all the other rows. If your
table spanned rows 1 to 100,

C1 [array formula]:
=SUM(LARGE((A$1:A$100=A1)*B$1:B$100,{1;2;3}))

C2 [array formula]:
=IF(A2lt;gt;A1,SUM(LARGE((A$1:A$100=A2)*B$1:B$100,{1;2 ;3})),quot;quot;)

Fill C2 down into C3:C100.Harlan - this works GREAT!!
Thanks much, Dorre

quot;Harlan Grovequot; gt; wrote in message oups.com...
gt; dorre wrote...
gt;gt;I've been struggling with a formula and could use some help.
gt;gt;Column A shows StudentNames and column B shows Grades. The columns are
gt;gt;sorted by A (ascend) then B (descend). I need a formula dragged down
gt;gt;column C that shows the SUM of the TOP 3 grades of each student in the top
gt;gt;row for that student.
gt;gt;
gt;gt;nb: not every student has 3 grades. For these, I'd like to sum whatever
gt;gt;grades they have.
gt;gt;
gt;gt;The result should look like this:
gt;gt;
gt;gt; A B C
gt;gt;1 Bill 67 167
gt;gt;2 Bill 50
gt;gt;3 Bill 50
gt;gt;4 Bill 46
gt;gt;5 Ed 85 160
gt;gt;6 Ed 75
gt;gt;7 Sue 43 43
gt;gt;8 Tom ...etc
gt; ...
gt;
gt; The easiest way to do this, though it's somewhat inefficient, requires
gt; a different formula in the top row than in all the other rows. If your
gt; table spanned rows 1 to 100,
gt;
gt; C1 [array formula]:
gt; =SUM(LARGE((A$1:A$100=A1)*B$1:B$100,{1;2;3}))
gt;
gt; C2 [array formula]:
gt; =IF(A2lt;gt;A1,SUM(LARGE((A$1:A$100=A2)*B$1:B$100,{1;2 ;3})),quot;quot;)
gt;
gt; Fill C2 down into C3:C100.
gt;