If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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..... |
#2
|
|||
|
|||
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..... |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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..... |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Is 12 the highest possible Flesch-Kincaid score (grammar checker) | RELory | General Discussion | 7 | November 19th, 2009 04:26 PM |
Quiz Test Maker | Tony Dondero | Running & Setting Up Queries | 0 | September 8th, 2005 11:00 PM |
Extracting highest values from a set of fields? | Dirtyweeker | Running & Setting Up Queries | 1 | May 8th, 2005 01:24 PM |
Union query with left join | Abdula Zakaria via AccessMonster.com | Running & Setting Up Queries | 4 | April 23rd, 2005 11:58 AM |
Modifying Tab Order | Joan McDonald | New Users | 7 | June 11th, 2004 11:19 PM |