Get highest scores multiple tests & students

[Ptyrider]

Have no idea if I can do this or how, my Excel skills are pretty basic;
help much appreciated. I have a series of tests and want to get the
highest score from multiple attempts by multiple students. I can create
a table like this:

test1 joe doe 95
test1 joe doe 85
test1 joe doe 75
test1 jane doe 96
test1 jane doe 82
test1 jane doe 83
test2 joe doe 92
test2 joe doe 86
test2 jane doe 93
test2 jane doe 76
.. . .

And want to end up with this:

test1 joe doe 95
test1 jane doe 96
test2 joe doe 92
test2 jane doe 93
.. . .

The next step would be to calculate averages per student, i. e. average
of all of joe doe's tests, average of jane doe's test. etc.

 

[T. Valko]

Try this:

............E...............F...........G............H
1.......test1.......joe doe........................
2.......test1.......jane doe......................
3.......test2.......joe doe.......................
4.......test2.......jane doe......................

For the max score enter this formula in G1 and copy down to G4:

=SUMPRODUCT(MAX((A$1:A$10=E1)*(B$1:B$10=F1)*C$1:C$10))

For the average enter this formula in H1 as an array using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(B$1:B$10=F1,C$1:C$10))

Copy down to H2.

Biff

 

[Ptyrider]

Try this:

<snip>

Wow! That worked beautifully!!! Such a fast response, too . . . would
have taken me weeks. THANK YOU!!! This will get good use.

Great newsgroup here. I'm glad to have discovered it for lurking.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

<snip>

Wow! That worked beautifully!!! Such a fast response, too . . . would
have taken me weeks. THANK YOU!!! This will get good use.

Great newsgroup here. I'm glad to have discovered it for lurking.