# Max, If, Large Functions

Microsoft Excel 2007
February 3, 2010 at 05:44:54
Specs: Windows Vista
 Hi all,i do really need helpam programming a student reportand 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

See More: Max, If, Large Functions

#1
February 3, 2010 at 06:39:25
 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.

Report •

#2
February 3, 2010 at 06:48:13
 thank you mr.derbyactually i have a list of 600 students and i have 25 classessi need to to know the highest 10 students in each classbeacuse am doing a reporti need to return a text " first " for the first student and " second for the second student am actually using vlookup please help mei have create a designwhen i choose number 1 from a listit will return for me a student name with all its marksand i have cell which must be filled up with first or second or third until the tenth studentregards,maram

Report •

#3
February 3, 2010 at 07:05:22
 it didnot work with me:(

Report •

Related Solutions

#4
February 3, 2010 at 08:08:52
 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?

Report •

#5
February 3, 2010 at 08:17:40
 this cell based on the highest studentlet me give you an example pleasei have a table like this1 tom 872 sam 903 bill 1004 toney 99.55 .........................until 600 hundred studentsnow my work need to know the 10 highest students in the schoolso it must be bill right coz he got 100so he is the first studentso in this cell which i told you a bout i need when to first or second or thirdlike i want to give the first highest geniuse student on the hall school presentsso first of all i need to know the first 10 highest students rightthen i need to return a value as first for bill and second for the second highest studenti hope that you have understand meplease help meregardsmaram

Report •

#6
February 3, 2010 at 09:07:16
 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:`=A3`All 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

Report •

#7
February 3, 2010 at 09:20:27
 hi mr. humarno not like this , its differentnow i have aschooland 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 studentso i need when the certificate is printed to have like champion student for the highest students in marksactually i need only to know the highest students marks on all the school from 600 studentsi tried to use the large and max functionbut it didnot work to know the 10 highest students in marksparents need to know whio is student number1 the second highest will be secondthe third highest will be thirduntil 10 th studentfrom the hall schoolit 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 firstsecond until tenth is this possibleplease help me regardsmaram

Report •

#8
February 3, 2010 at 11:48:19
 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 followingmarks rank name of the rank50 5 599 2 second10 6 699.5 1 first78 4 490 3 thirddo you think its alrightthank you very muchregards,maram

Report •

#9
February 3, 2010 at 11:58:46
 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)

Report •

#10
February 3, 2010 at 13:46:36
 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 Eand start at row 2and you have a lookup table for 1 to 10 and First to Tenth in cells K2 to L11Use 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

Report •

#11
February 3, 2010 at 14:56:39
 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

Report •

#12
February 3, 2010 at 22:23:30
 i have a big problem i used the rank but if i have 99.5 , 99.5, 99it will return the rank as 1,1,3not 1,1,2i have round all the nubers coz its not allowed to have any digitsso what must i doplease help meregards, maram

Report •

#13
February 4, 2010 at 05:04:48
 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.5Regards

Report •

#14
February 4, 2010 at 05:10:13
 hello mr. humarmr. humar can i send you my sheet nowi have done a small example and their is a problem which i dont understandcan you please help methank you very much regards,maram

Report •

#15
February 4, 2010 at 05:44:13
 mr. humar i want to attach for you the file but i dont know howi need you to see itplease,regards,maram

Report •

#16
February 4, 2010 at 06:01:13
 =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 onlyi hope that you have under stand mebut theirs aproblem when i change the numbers of the marksplease help meregards,maram

Report •

#17
February 4, 2010 at 07:35:27
 Hi,This formula uses a lookup table but also adds 'duplicate' to equal ranks - text only, as requiredThis 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 DThe lookup table is in cells N3 to O13``` N 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 duplicate1812First duplicateFourthEighth22In 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

Report •

#18
February 4, 2010 at 12:01:53
 Thank you very much mr. humaram going to send you my email in private messageand am going to try this formula.thank you very muchregards,maram

Report •

#19
February 5, 2010 at 08:14:00
 hello mr. humari have send you the email with the sheeti hope really that you can help meregards,maram

Report •

#20
February 6, 2010 at 04:58:24
 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

Report •

#21
February 6, 2010 at 05:51:55
 Hello Mr. Humar;I really dont know what to sayreally thank you very much you helped me alot.you are Genius .i put the code in my programand its working perfectlythank you thank you very muchRegards,Maram

Report •

#22
February 6, 2010 at 06:32:43
 Hello Maram,Really pleased to hear that its working.Thanks for the feedback,RegardsHumar

Report •

#23
February 6, 2010 at 07:30:12

Report •

#24
February 6, 2010 at 07:52:25
 Am So Sorry Mr. Humarit's working knowam sorry for the big miss i didthe problem was that am doing all the classes in the same pageand i need the rank for each class sepreatlyi changed the rank and the lookup and every thingbut i didnot changed the countif rankingso it was giving me same and same ranking for the first classsorry for not observing that before and making this missi fixit its working nowthank you very muchi really dont know what to sayyou really helped me very muchall my respect for youi really hope that i can be your studentRegards,maram

Report •

#25
February 6, 2010 at 07:57:29
 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

Report •

#26
February 6, 2010 at 08:26:53
 you are right mr.humar the countif rank was my problemthank you very much,regards,maram

Report •