Highest Score for each Test

Is there a formula/function to to this? Each student get 3 attempts at
a test and I take the highest score. I want a formula that will display
the highest score for each student for for each different test...so the
end result will hide the other two lower score and a student's name will
never be displayed twice for the same test. I have no idea how to
approach this or whether it's even possible. I'm thinking maybe an
array or lookup, but maybe it's as simple as a filter. Do any of you
know how to do something like this? I'd appreciate any tips and pointers.

Ed

Spreadsheet:
ColA ColB ColC
StudentName TestName Score
Student1 Test1 60
Student1 Test1 80
Student1 Test1 70
Student2 Test1 40
Student2 Test1 90
Student2 Test1 75
Student1 Test2 50
Student1 Test2 85
Student1 Test2 75
Student2 Test2 55
Student2 Test2 65
Student2 Test2 80
Etc.....

There is a feature of Excel that does exactly what you want. Its called a
Pivot Table. You get to it by pulling-down:

Data Pivot Table...

Using it you can organize a table by student by test to give the max of score.
--
Gary''s Student


"abqhusker" wrote:

Is there a formula/function to to this? Each student get 3 attempts at
a test and I take the highest score. I want a formula that will display
the highest score for each student for for each different test...so the
end result will hide the other two lower score and a student's name will
never be displayed twice for the same test. I have no idea how to
approach this or whether it's even possible. I'm thinking maybe an
array or lookup, but maybe it's as simple as a filter. Do any of you
know how to do something like this? I'd appreciate any tips and pointers.

Ed

Spreadsheet:
ColA ColB ColC
StudentName TestName Score
Student1 Test1 60
Student1 Test1 80
Student1 Test1 70
Student2 Test1 40
Student2 Test1 90
Student2 Test1 75
Student1 Test2 50
Student1 Test2 85
Student1 Test2 75
Student2 Test2 55
Student2 Test2 65
Student2 Test2 80
Etc.....

If your data is in nice sorted order, you could use data|subtotals (twice).

Once by the student name and once by the testname. Make sure you use Max as the
function.

But you may want to invest a little time in learing pivottables.

Select your range
data|pivottable
follow the wizard until you get to a dialog with a Layout button on it.
hit that Layout button

drag the student name button to the row field
drag the test name button to the row field
drag the score button to the data field
double click on that score button and choose max.

And finish up that wizard.

abqhusker wrote:

Is there a formula/function to to this? Each student get 3 attempts at
a test and I take the highest score. I want a formula that will display
the highest score for each student for for each different test...so the
end result will hide the other two lower score and a student's name will
never be displayed twice for the same test. I have no idea how to
approach this or whether it's even possible. I'm thinking maybe an
array or lookup, but maybe it's as simple as a filter. Do any of you
know how to do something like this? I'd appreciate any tips and pointers.

Ed

Spreadsheet:
ColA ColB ColC
StudentName TestName Score
Student1 Test1 60
Student1 Test1 80
Student1 Test1 70
Student2 Test1 40
Student2 Test1 90
Student2 Test1 75
Student1 Test2 50
Student1 Test2 85
Student1 Test2 75
Student2 Test2 55
Student2 Test2 65
Student2 Test2 80
Etc.....

--

Dave Peterson

Thanks, Gary and Dave. It works like a charm. For some reason pivot
tables are confusing to me, I mean, I know how to find the wizard and
make one, but I never understood reasoning behind what field to drop in
what drop area. I understand pivot tables much better now than I've
ever did because of you two helping me with this problem. Pivot table
was the farthest thing from my mind when I requested the help last
night. You guys are geniuses. Thanks again for the prompt and expert
response.

Ed

abqhusker wrote:
Is there a formula/function to to this? Each student get 3 attempts at
a test and I take the highest score. I want a formula that will display
the highest score for each student for for each different test...so the
end result will hide the other two lower score and a student's name will
never be displayed twice for the same test. I have no idea how to
approach this or whether it's even possible. I'm thinking maybe an
array or lookup, but maybe it's as simple as a filter. Do any of you
know how to do something like this? I'd appreciate any tips and pointers.

Ed

Spreadsheet:
ColA ColB ColC
StudentName TestName Score
Student1 Test1 60
Student1 Test1 80
Student1 Test1 70
Student2 Test1 40
Student2 Test1 90
Student2 Test1 75
Student1 Test2 50
Student1 Test2 85
Student1 Test2 75
Student2 Test2 55
Student2 Test2 65
Student2 Test2 80
Etc.....

Now you've done it....vbg:

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

abqhusker wrote:

Thanks, Gary and Dave. It works like a charm. For some reason pivot
tables are confusing to me, I mean, I know how to find the wizard and
make one, but I never understood reasoning behind what field to drop in
what drop area. I understand pivot tables much better now than I've
ever did because of you two helping me with this problem. Pivot table
was the farthest thing from my mind when I requested the help last
night. You guys are geniuses. Thanks again for the prompt and expert
response.

Ed

abqhusker wrote:
Is there a formula/function to to this? Each student get 3 attempts at
a test and I take the highest score. I want a formula that will display
the highest score for each student for for each different test...so the
end result will hide the other two lower score and a student's name will
never be displayed twice for the same test. I have no idea how to
approach this or whether it's even possible. I'm thinking maybe an
array or lookup, but maybe it's as simple as a filter. Do any of you
know how to do something like this? I'd appreciate any tips and pointers.

Ed

Spreadsheet:
ColA ColB ColC
StudentName TestName Score
Student1 Test1 60
Student1 Test1 80
Student1 Test1 70
Student2 Test1 40
Student2 Test1 90
Student2 Test1 75
Student1 Test2 50
Student1 Test2 85
Student1 Test2 75
Student2 Test2 55
Student2 Test2 65
Student2 Test2 80
Etc.....

--

Dave Peterson