4 columns, sum the 3 highest values

[mjmaguire]

Using Excel 2000

I'm a college professor. I allow my students to take 4 tests but I only use the grades of the 3 tests on which they perform best.

I have a spreadsheet set up with 4 columns of results (values from 0-100).

I need excel to ignore the lowest grade and sum the three highest scores from each set of four columns into a new column that displays the total score out of 300.



name test1 test2 test3 test4 total
J.Doe 80 80 30 80 240



thanks for any help you can give.
i have a hundred students and doing this by hand is becoming tedious.


Mike Maguire

[Aladin Akyurek]

On 2002-05-09 11:51, mjmaguire wrote:
Using Excel 2000

I'm a college professor. I allow my students to take 4 tests but I only use the grades of the 3 tests on which they perform best.

I have a spreadsheet set up with 4 columns of results (values from 0-100).

I need excel to ignore the lowest grade and sum the three highest scores from each set of four columns into a new column that displays the total score out of 300.



name test1 test2 test3 test4 total
J.Doe 80 80 30 80 240



thanks for any help you can give.
i have a hundred students and doing this by hand is becoming tedious.


Mike Maguire

Something that I also compute!

Lets say that the scores are in row in B1:E1 for Doe.

In F1 enter:

=IF(COUNT(B1:E1)>3,SUM(B1:E1)-MIN(B1:E1),SUM(B1:E1))

and copy down for other students.

I added the condition that you get a sum tout court, when there 3 or less scores are available.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-09 11:58 ]

[Mark W.]

=SUM(LARGE(B1:E1,{1,2,3}))

[ This Message was edited by: Mark W. on 2002-05-09 12:11 ]

[mjmaguire]

Thanks for your help.