How do I extract the top 5 percent from a list?
 

I have a list of marks of students (numbers) and I want to extract the top 5
percentage from the list

Hi,
U can use "Data / Sort" tools.

If any problem, revert.

Regards

Akhilesh Dalia

akhileshdalia<athotmail<dotcom

"Anitha" wrote:

I have a list of marks of students (numbers) and I want to extract the top 5
percentage from the list

I take it that you want the names of the students along with their marks
to figure in the list. There are a couple options.

A formula sytem:

Let A5:B25 house the following sample, including the headers Student and
Mark...

{"Student","Mark";
"dawn",35;
"damon",23;
"bob",25;
"chris",22;
"christine",21;
"ian",32;
"john",35;
"anitha",35;
"brian",33;
"laurent",29;
"daniel",27;
"dennis",32;
"tom",26;
"thomas",29;
"martin",31;
"linda",30;
"olga",35;
"dilip",31;
"mete",25;
"bilge",31}

C6, copy down:

=RANK(B6,$B$6:$B$25)+COUNTIF(B6:$B$6,B6)-1

This ranks the marks.

D5:

="Top "&SUM(E3:E4)&" Students"

D6, copy to E6 then down:

=IF(ROW()-ROW(D$6)+1<=$E$3+$E$4,INDEX(A$6:A$25,MATCH(ROW()-ROW($D$6)+1,$C$6:$C$25,0)),"")

Parameters, given & calculated

E1:

=COUNT(B6:B26)

Counts the available marks.

E2: 5%

E3:

=ROUNDUP(E1*E2,0)

Determines N, taking 5% of the available marks.

E4:

=MAX(IF(INDEX(B6:B20,MATCH(E3,C6:C20,0))=B6:B20,C6 :C20))-E3

which must be confirmed with control+shift+enter instead of the usual enter.

The latter formula determines the ties of the Nth value calculated in E3.

The area in D:E from row 6 downwards will contain the desired list of
Top 5% of the students along with their marks.

Other options: Advanced Filter with a computed criteria, AutoFilter, and
Pivot tables.

Anitha wrote:
I have a list of marks of students (numbers) and I want to extract the top 5
percentage from the list

another method is find percentile(95%) and do advance filter


Akhilesh Dalia wrote in message
...
Hi,
U can use "Data / Sort" tools.

If any problem, revert.

Regards

Akhilesh Dalia

akhileshdalia<athotmail<dotcom

"Anitha" wrote:

I have a list of marks of students (numbers) and I want to extract the
top 5
percentage from the list