Hi all, i do really need help

am programming a student report

and i need to know the first 10 student in a class plus returning words ( first , second , third ..... tenth )

is this impossible in excel please help meregards,

maram

I'm not sure what you want the output to look like. If you are simply going to have a list of the Top 10 scores somewhere in the spreadsheet, then I'm not sure why you need to "return" the words First, Second, etc. Simply put these words in the column next to where you extract the list of names associated with the Top 10 scores.

To extract the names from the list, try this example and adjust as required:

Put your Scores in A1:A16.

Put your Names in B1:B16Put this formula in C1 and drag it down to C10:

=VLOOKUP(LARGE($A$1:$A$16,ROW()),$A$1:$B$16,2,0)

It will pull the Names associated with the Top 10 scores.

The ROW() function will increment for each Row (1, 2, 3, etc.) and use that number for the LARGE function.

With First, Second, Third, etc. in D1:D10, you'll have your list of names and the "words".

If I'm missing something in your question, please let me know.

thank you mr.derby actually i have a list of 600 students

and i have 25 classess

i need to to know the highest 10 students in each class

beacuse am doing a report

i need to return a text " first " for the first student and " second for the second student

am actually using vlookup

please help me

i have create a design

when i choose number 1 from a list

it will return for me a student name with all its marks

and i have cell which must be filled up with first or second or third until the tenth student

regards,

maram

it didnot work with me

:(

re: " and i have cell which must be filled up with first or second or third until the tenth student"What is "first or second or third" based on?

this cell based on the highest student let me give you an example please

i have a table like this

1 tom 87

2 sam 90

3 bill 100

4 toney 99.5

5 .........................

until 600 hundred students

now my work need to know the 10 highest students in the school

so it must be bill right coz he got 100

so he is the first student

so in this cell which i told you a bout i need when to first or second or third

like i want to give the first highest geniuse student on the hall school presents

so first of all i need to know the first 10 highest students right

then i need to return a value as first for bill and second for the second highest student

i hope that you have understand me

please help me

regards

maram

Hi, Like DerbyDad03, I do not understand why you need a formula to cretae text "First", "Second" etc.

This text is only going to occur once - in a column to the left of names and scores.For this example each class has results in the same columns but on different worksheets. You could have all results on the same worksheet but in different columns.

The results look like this:

A B C D E 1 Class #1 Class #2 2 Rank Name Score Name Score 3 First Name Q 98 Name X 95 4 Second Name D 98 Name V 94 5 Third Name N 94 Name E 93 6 Fourth Name E 89 Name L 92 7 Fifth Name Z 88 Name M 91 8 Sixth Name U 88 Name Z 86 9 Seventh Name T 83 Name G 86 10 Eighth Name F 82 Name C 86 11 Ninth Name R 81 Name A 84 12 Tenth Name V 78 Name N 80

The names and scores are all filled using VLOOKUP()

In cell B3 use this formula:=VLOOKUP(1,Sheet2!$D$3:$E$28,2,FALSE)

In Cell C3 use this formula:=VLOOKUP(B3,Sheet2!$A$3:$B$28,2,FALSE)The data on sheet2 is organized like this:

A B C D E 1 Class #1 2 Name Score Extended score Rank Name 3 Name A 78 78.00003 11 Name A 4 Name B 44 44.00004 18 Name B 5 Name C 63 63.00005 12 Name C 6 Name D 98 98.00006 2 Name D

The reason for the extended score is to stop RANK() returning two ranks the same.

If two individuals have the same rank, the VLOOKUP() function will not return the name of the second individual.The extended rank is created by adding a very small value to each score to make it unique.

In C3 enter this:=B3+ROW(A3)/100000

This is the row number divided by 100,000, never enough to reach 1 and alter a score's position in the table, only enough to make two equal scores different and be ranked consecutively.

You may need to use a smaller value, e.g., divide by 1,000,000 if scores have one decimal place.The reason for the second column of names is to enable the name to be returned by VLOOKUP using the rank.

The real score is returned from column B, using VLOOKUP and the individual's name.

The rank formula in Cell D3 is:=RANK(C3,$C$3:$C$28)

The formula in E3 is just:=A3All formulas are just dragged down, with the exception of the first result column where cell B3 contains the rank number to lookup - 1 in B3

=VLOOKUP(1,Sheet2!$D$3:$E$28,2,FALSE), which has to be changed to 2, 3 , 4 etc. to 10.

You could use the approach DerbyDad03 suggested using the Row number to create the number.The results data in columns B and C can be copied and pasted to columns D and E and then use Find-replace to change the worksheet name.

Regards

hi mr. humar no not like this , its different

now i have aschool

and i need the parent to know the order of their students in their classes they want want to know who is student number 1

because he will be the most smart student

so i need when the certificate is printed to have like champion student for the highest students in marks

actually i need only to know the highest students marks on all the school from 600 students

i tried to use the large and max function

but it didnot work to know the 10 highest students in marks

parents need to know whio is student number1

the second highest will be second

the third highest will be third

until 10 th student

from the hall school

it will be difficult to see the final mark for each student to the 10 highest student and then i want to return a text as first

second until tenth

is this possible

please help me

regards

maram

Hi Mr.derby & Mr. Humar, i have resolve the problem with a solution of two parts.

first i have the final coloumn marks right. so i add another coloumn called rank which is indepented on the final marks

=RANK(D2;$D$2:$D$7;0)

then i made an if statement at the next column as the following

=IF(E2=1;"first";IF(E2=2;"second";IF(E2=3;"third";RANK(E2;$E$2:$E$7;1))))

so the sheet now as the following

marks rank name of the rank

50 5 5

99 2 second

10 6 6

99.5 1 first

78 4 4

90 3 third

do you think its alright

thank you very much

regards,

maram

You need First through Tenth, right? So you need 10 Nested IF's, right?

10 nested IF's will work in 2007, but your spreadsheet will not be backward compatible to any earlier version due to the 7 Nested IF's limit prior to 2007.

Besides, 10 Nested IF's makes for a really long (and ugly) formula.

I'd opt for a VLOOKUP table of the type:

1 First 2 Second 3 Third 4 Fourth 5 Fifth etc.Then I would use this to pull in the words:

=VLOOKUP(E2,

lookup_array, 2, 0)

Hi, I would go with DerbyDad03's solution to your First, Second ... text requirement.

VLOOKUP() is well suited to this type of issue - much better than a series of nested IF's.

To include the rank do this:

If your Rank results are in column E

and start at row 2

and you have a lookup table for 1 to 10 and First to Tenth in cells K2 to L11

Use this formula in cell F2:=IF(ISNA(VLOOKUP(E2,$K$2:$L$11,2,FALSE)),E2,VLOOKUP(E2,$K$2:$L$11,2,FALSE))This gives you either text 'First' etc. or rank numbers from 11 onwards.

Regards

Hi another option without a VLOOKUP() table, and avoiding that nasty nested IF formula is this:

=IF(E2<11,CHOOSE(E2,"First","Second","Third","Fourth","Fifth",

"Sixth","Seventh","Eighth","Ninth","Tenth"),E2)This is all one formula - I just split it onto two lines for ease of viewing.

Regards

i have a big problem i used the rank but if i have 99.5 , 99.5, 99

it will return the rank as 1,1,3

not 1,1,2

i have round all the nubers coz its not allowed to have any

digitsso what must i do

please help me

regards,

maram

Hi, The Rank function is correct.

In your example, the individual with a score of 99 is third because there are two people with better scores ahead of him/her, both with 99.5

Regards

hello mr. humar mr. humar can i send you my sheet now

i have done a small example and their is a problem

which i dont understand

can you please help me

thank you very much

regards,

maram

mr. humar i want to attach for you the file but i dont know how i need you to see it

please,

regards,

maram

=IF(COUNTIF($F$2:$F$22;"First")>1;IF(F2="First";"First duplicate";0);IF(COUNTIF($F$2:$F$22;"Second")>1;IF(F2="Second";"Second duplicate";0);IF(COUNTIF($F$2:$F$22;"Third")>1;IF(F2="Third";"Third duplicate";0);IF(COUNTIF($F$2:$F$22;"Fourth")>1;IF(F2="Fourth";"Fourth duplicate";0);IF(COUNTIF($F$2:$F$22;"Fifth")>1;IF(F2="Fifth";"fifth duplicate";0);IF(COUNTIF($F$2:$F$22;"Sixth")>1;IF(F2="Sixth";"Sixth duplicate";0);IF(COUNTIF($F$2:$F$22;"Seventh")>1;IF(F2="Seventh";"Seventh duplicate";0);IF(COUNTIF($F$2:$F$22;"eighth")>1;IF(F2="eighth";"eighth duplicate";0);IF(COUNTIF($F$2:$F$22;"Ninth")>1;IF(F2="Ninth";"Ninth duplicate";0);IF(COUNTIF($F$2:$F$22;"Tenth")>1;IF(F2="Tenth";"Tenth duplicate";IF(E2=1;"First";IF(E2=2;"Second";IF(E2=3;"Third";IF(E2=4;"Fourth";IF(E2=5;"Fifth";IF(E2=6;"Sixth";IF(E2=7;"Seventh";IF(E2=8;"eighth";IF(E2=9;"Ninth";IF(E2=10;"Tenth";RANK(E2;$E$2:$E$22;1)))))))))))))))))))))) this my code i need from first to tenth and i need if their is many first to typit as first duplicate but after the tenth i need to typit as numbers only

i hope that you have under stand me

but theirs aproblem when i change the numbers of the marks

please help me

regards,

maram

Hi, This formula uses a lookup table but also adds 'duplicate' to equal ranks - text only, as required

This is the formula:

=IF(VLOOKUP(D3,$N$3:$O$13,2)="X",D3,VLOOKUP(D3,$N$3:$O$12,2,FALSE))

& IF(D3<11,IF(COUNTIF($D$3:D25,D3)>1," duplicate",""),"")The formula has been split onto two lines for ease of viewing.

The rank is in column D

The lookup table is in cells N3 to O13N O 3 1 First 4 2 Second 5 3 Third 6 4 Fourth 7 5 Fifth 8 6 Sixth 9 7 Seventh 10 8 Eighth 11 9 Ninth 12 10 Tenth 13 11 X

Some of my sample results look like this:

Tenth duplicate

18

12

First duplicate

Fourth

Eighth

22In the example rank 22 was duplicated, but no 'duplicate' message is added.

If this does not do what you want, you can send me a private message with your e-mail and I will provide you with an e-mail address to send your workbook to.

Do not post your e-mail in an open response.

Regards

Thank you very much mr. humar am going to send you my email in private message

and am going to try this formula.

thank you very much

regards,

maram

hello mr. humar i have send you the email with the sheet

i hope really that you can help me

regards,

maram

Hi, You sent me the spreadsheet and I was able to use the formula given in Response #17 to add the word 'duplicate' to all duplicates in the top ten ranks - the ones reported as First, Second etc.

You then asked if duplicate could be used for all duplicates, not just the top ten.

As there is now no need to test for the top ten before deciding whether to add 'duplicate' to the Rank, the formula can be simplified as follows:

=IF(VLOOKUP(K3,$L$3:$M$13,2)="X",K3,VLOOKUP(K3,$L$3:$M$13,2,FALSE))& IF(COUNTIF($K$3:$K$23,K3)>1," duplicate","")The original Ranks are in column K, the lookup table for converting Ranks 1 - 10 to First - Tenth is in cells L3 to M13.

The formula is effectively two parts separated by &.

Before & the formula decides whether to use the Rank number or to do a conversion to Text and after & the formula decides whether to add 'duplicate' or not based on the COUNTIF() function.Regards

Hello Mr. Humar; I really dont know what to say

really thank you very much you helped me alot.

you are Genius .

i put the code in my program

and its working perfectly

thank you thank you very much

Regards,

Maram

Hello Maram, Really pleased to hear that its working.

Thanks for the feedback,

Regards

Humar

Mr. Humar its only taking the fourth and the tenth as duplicated only please help me

Am So Sorry Mr. Humar it's working know

am sorry for the big miss i did

the problem was that am doing all the classes in the same page

and i need the rank for each class sepreatly

i changed the rank and the lookup and every thing

but i didnot changed the countif ranking

so it was giving me same and same ranking for the first class

sorry for not observing that before and making this miss

i fixit its working now

thank you very much

i really dont know what to say

you really helped me very much

all my respect for you

i really hope that i can be your student

Regards,

maram

Hi, The formula worked OK on the spreadsheet you sent me.

Check that you haven't lost the $ signs in the formula, also make sure that the COUNTIF() refers to the whole range of ranks.

When you use the formula on your real data, the ranges have to be changed to match.

IF(COUNTIF($K$3:$K$23,K3)>1," duplicate","")

The $K$3:$K$23 range must match the full range of the RanksClick in the formula inside the formula bar and look at which cells are outlined in color and make sure that they cover the whole range and don't just stop part way down the list of Ranks.

Regards

you are right mr.humar the countif rank was my problem

thank you very much,

regards,

maram

Ask Your Question

Weekly Poll