logical functions utilizing percentiles

[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!

 

[Joel]

One soilution would be to create a new table of the rang of each student.
Put the following formulas starting in row 1 in two new columns and copy down
the column

Second column - column Y
1, 2, 3 up to 1000
First Column - colun X
=Range(Y1,B1:B1000) where column B contains the grades.

I create the columns backwards to make the VLOOKUP function simplier
Now create a lookup into the table

Put in column C next to student grades
=Vlookup(B1,X1:Y1000,2) where B is the gade and X1:Y1000 is the table of
grades and rank.

 

[Mike H]

Hi,

I'm not sure I fully understand but I think you want to extract the names of
students who are in the top 20 Percentile for all the tests. Try this

=IF(AND(B1>=PERCENTILE($B$1:$B$10,0.8),C1>=PERCENTILE($C$1:$C$10,0.8),D1>=PERCENTILE($D$1:$D$10,0.8)),A1,"")

Drag down as required

Mike

 

[Shane Devenshire]

Hi,

You could try this

=IF((B1>PERCENTILE(B$1:B$21,0.8))*(C1>PERCENTILE(C$1:C$21,0.8))*(D1>PERCENTILE(D$1$21,0.8))=1,A1,"")

 

[Herbert Seidenberg]

Excel 2007 PivotTable
A graphical solution.
Iterate same as AND.
http://www.mediafire.com/file/wnw5gfdzdmw/06_28_09.xlsx

 

[Dan D]

Shane & Mike-- When I ran your functions over my data, they did not quite
match up with what I get when i do manual sorts. I am not expert enough to
parse your functions precisely, but perhaps you can tell me if they should
match my process.

keep in mind, i'm not (necessarily) looking for the top 20 percentile of
test 1 test 2 & test 3. It is only important that Test 1 is in the top 20
Percentile. From that universe I take the top 20% of the remaining
candidates. Then from that further limited universe, I take the top 20%. A
student can be in a relatively low percentile for test 3 relative to all the
takers -- but still make the cut if he was in the top of test 1 and then test
2.

Thanks!

 

[Dan D]

To further clarify -- Let's say there were 500 students. I am looking for
the 100 students who scored highest in test 1. Then of those 100, the 20 who
scored highest, then of those 20, the highest 4.

So you could score in the top 20% percentile of each test but not be in the
top 4, b/c you were not in the top 20th percentile of the new subgroup.

I usually will sort by the 500 scores of test 1 and essentially delete the
bottom 80% , then sort by test 2 and delete the bottom 80%, then sort by test
3 and delete the bottom 80%.

Hope that's clear -- Thanks.

 

[Dan D]

Actually -- I redid it and it did work! Thank you so much for your help!