Max, If, Large Functions

Microsoft Excel 2007
February 3, 2010 at 05:44:54
Specs: Windows Vista
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 me


See More: Max, If, Large Functions

Report •

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:B16

Put this formula in C1 and drag it down to C10:


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 •

February 3, 2010 at 06:48:13
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


Report •

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

Report •

Related Solutions

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 •

February 3, 2010 at 08:17:40
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


Report •

February 3, 2010 at 09:07:16

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:

In Cell C3 use this formula:

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:


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:


The formula in E3 is just:

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

, 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.


Report •

February 3, 2010 at 09:20:27
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


Report •

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


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


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


Report •

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

Then I would use this to pull in the words:

=VLOOKUP(E2, lookup_array, 2, 0)

Report •

February 3, 2010 at 13:46:36

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:


This gives you either text 'First' etc. or rank numbers from 11 onwards.


Report •

February 3, 2010 at 14:56:39
Hi another option without a VLOOKUP() table, and avoiding that nasty nested IF formula is this:

This is all one formula - I just split it onto two lines for ease of viewing.


Report •

February 3, 2010 at 22:23:30
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

so what must i do

please help me



Report •

February 4, 2010 at 05:04:48

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


Report •

February 4, 2010 at 05:10:13
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


Report •

February 4, 2010 at 05:44:13
mr. humar i want to attach for you the file but i dont know how

i need you to see it




Report •

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 only

i hope that you have under stand me

but theirs aproblem when i change the numbers of the marks

please help me


Report •

February 4, 2010 at 07:35:27

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

This is the formula:
& 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 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 duplicate
First duplicate

In 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.


Report •

February 4, 2010 at 12:01:53
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


Report •

February 5, 2010 at 08:14:00
hello mr. humar

i have send you the email with the sheet

i hope really that you can help me


Report •

February 6, 2010 at 04:58:24

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.


Report •

February 6, 2010 at 05:51:55
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



Report •

February 6, 2010 at 06:32:43
Hello Maram,

Really pleased to hear that its working.

Thanks for the feedback,



Report •

February 6, 2010 at 07:30:12
Mr. Humar its only taking the fourth and the tenth as duplicated only

please help me

Report •

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



Report •

February 6, 2010 at 07:57:29

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 Ranks

Click 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.


Report •

February 6, 2010 at 08:26:53
you are right mr.humar

the countif rank was my problem

thank you very much,



Report •

Ask Question