Formula for top n

[dorre]

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...
Dorre

 

[Harlan Grove]

dorre wrote...

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

....

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(A2<>A1,SUM(LARGE((A$1:A$100=A2)*B$1:B$100,{1;2;3})),"")

Fill C2 down into C3:C100.

 

[dorre]

Harlan - this works GREAT!!
Thanks much, Dorre

dorre wrote...

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

...

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(A2<>A1,SUM(LARGE((A$1:A$100=A2)*B$1:B$100,{1;2;3})),"")

Fill C2 down into C3:C100.