Need help in Excel 2007 - urgent

[Vikram]

I have a sorted data of 450 learners with name and percentage. Columns are
Name Percentage Result
Mac 86 Pass
Richard 67 Fail
Jack 91 Fail
......
I need a seperate list of TOP 5 LEARNERS with name columns.
I have tried Pivote table and Large function but it dont work as I can
filter on numeric values (as top 10) but i need top 5 and that too top 5
names alone.
NOTE:Topper Name List should not contain Fail students even if they have top
marks.
Please help asap.
Regards
Vikram

 

[Mike H]

Hi,

=INDEX(A1:A20,MATCH(MAX(IF(C1:C20="Pass",B1:B20)),B1:B20,0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Ashish Mathur]

Hi,

Try this. Assume that your data is in range B313 (including the header
row say Name, Percentage and Result). In E16:F16, enter Result and
Condition. In E17, enter Pass and in F17 enter =RANK(C4,$C$4:$C$13)<=5. On
B21, type Name

Now go to Data > filter > Advanced Filter. Select copy to another location
and in the list range, select B313. In the criteria range, select
E16:F17. In the copy to box, select cell B21 and now click on OK.

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

What is the challenge with using a pivot. Instead of top 10, why cant you
select top 5. So you can drag name to the row area, result to the column
area and result to the data area. Now filter the result on Pass. In the
name columns in the row area, select top 5

The only problem with this solution is that the pivot table will give you
both the columns - name and amount whereas you want names only.

Post back to let me know if this is OK. If not, I will suggest another
solution.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com