Hi,

You could try this

=IF((B1PERCENTILE(B$1:B$21,0.8))*(C1PERCENTILE(C $1:C$21,0.8))*(D1PERCENTILE(D$1:D$21,0.8))=1,A1," ")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Dan D" wrote:

Lets 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 €“ Im 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!