Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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. "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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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=PERCENT ILE($C$1:$C$10,0.8),D1=PERCENTILE($D$1:$D$10,0.8) ),A1,"") Drag down as required Mike "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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
Excel 2007 PivotTable
A graphical solution. Iterate same as AND. http://www.mediafire.com/file/wnw5gfdzdmw/06_28_09.xlsx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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! "Shane Devenshire" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
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" wrote: 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! "Shane Devenshire" wrote: 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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical functions utilizing percentiles
Actually -- I redid it and it did work! Thank you so much for your help!
"Shane Devenshire" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logical functions If, And | Excel Discussion (Misc queries) | |||
Logical functions IF & AND | Excel Discussion (Misc queries) | |||
Logical Functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions |