Get highest scores multiple tests & students
 

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.

Get highest scores multiple tests & students
 

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" wrote in message
oups.com...
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.

Get highest scores multiple tests & students
 

T. Valko wrote:
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.

Get highest scores multiple tests & students
 

You're welcome. Thanks for the feedback!

Biff

"Ptyrider" wrote in message
ups.com...

T. Valko wrote:
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.