Get highest scores multiple tests & students

P

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

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
 
P

Ptyrider

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

T. Valko

You're welcome. Thanks for the feedback!

Biff

Ptyrider said:
<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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top