D
Dan D
Let’s say I have a spreadsheet with over 1,000 rows of student information.
Column A has the name of the students. Columns B, C & D contain the results
of Test 1, Test 2 and Test 3, respectively.
I need to find the students in the top 20th percentile based on the scores
of Test 1. Among those in the top 20th percentile of Test 1, I am looking
for the top 20th percentile of students based on scores of Test 2, and among
that group, the top 20th percentile based on scores of Test 3. Until
recently, I had been doing this manually. I would sort the table by Test 1;
select the top 20% of the rows and paste into a new table. Then I sort those
in the new table by Test 2. I take the top 20% of those and put into a 3rd
column…
Is there a way I could do this 3 step test by using Excel functions, like
percentilerank & logical statements?
Best case scenario, I paste in a table of 1000 students and, in another part
of the spreadsheet, I see the names of the students who meet the 3 test
criteria automatically.
Just to be clear once more – I’m not looking for students who are in the top
20th percentile of tests 1, 2 and 3. Instead, I am looking for the top 20th
percentile of test 3 --> in the universe of students in the top 20th
percentile for test 2 --> in the universe of students in the top 20th
percentile for test 1.
Any help would be much appreciated!
Column A has the name of the students. Columns B, C & D contain the results
of Test 1, Test 2 and Test 3, respectively.
I need to find the students in the top 20th percentile based on the scores
of Test 1. Among those in the top 20th percentile of Test 1, I am looking
for the top 20th percentile of students based on scores of Test 2, and among
that group, the top 20th percentile based on scores of Test 3. Until
recently, I had been doing this manually. I would sort the table by Test 1;
select the top 20% of the rows and paste into a new table. Then I sort those
in the new table by Test 2. I take the top 20% of those and put into a 3rd
column…
Is there a way I could do this 3 step test by using Excel functions, like
percentilerank & logical statements?
Best case scenario, I paste in a table of 1000 students and, in another part
of the spreadsheet, I see the names of the students who meet the 3 test
criteria automatically.
Just to be clear once more – I’m not looking for students who are in the top
20th percentile of tests 1, 2 and 3. Instead, I am looking for the top 20th
percentile of test 3 --> in the universe of students in the top 20th
percentile for test 2 --> in the universe of students in the top 20th
percentile for test 1.
Any help would be much appreciated!