Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help in Excel 2007 - urgent
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help in Excel 2007 - urgent
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 "Vikram" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help in Excel 2007 - urgent
Hi,
Try this. Assume that your data is in range B3:D13 (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 B3:D13. 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 "Vikram" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help in Excel 2007 - urgent
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 "Vikram" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help in Excel 2007 - urgent | New Users to Excel | |||
Need help in Excel 2007 - urgent | New Users to Excel | |||
Urgent: Excel 2003 - 2007 conflict! | Excel Discussion (Misc queries) | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
Urgent! How do I create mailing labels with Excel 2007? | Excel Discussion (Misc queries) |