ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Ranking solution (https://www.excelbanter.com/excel-discussion-misc-queries/137923-ranking-solution.html)

Will
Ranking solution
 
Dear All,

I would like to rank my students but I do face prob if some of them have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

T. Valko
Ranking solution
 
Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of them have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Teethless mama
Ranking solution
 
Assuming your data A1:B5
Select A1:B5 Data Sort Sort by select Column B Decending OK
In C1: enter 1
In C2: =IF(B2<B1,C1+1,C1)
Copy from C2 to C5

The result will be 1,2,2,3,4 which are Mark, Simon, Stella, Jo, Jean


"Will" wrote:

Dear All,

I would like to rank my students but I do face prob if some of them have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Will
Ranking solution
 
Mama,

I am using xcel2003. Hee..

"Teethless mama" wrote:

Assuming your data A1:B5
Select A1:B5 Data Sort Sort by select Column B Decending OK
In C1: enter 1
In C2: =IF(B2<B1,C1+1,C1)
Copy from C2 to C5

The result will be 1,2,2,3,4 which are Mark, Simon, Stella, Jo, Jean


"Will" wrote:

Dear All,

I would like to rank my students but I do face prob if some of them have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Teethless mama
Ranking solution
 
Have you try it?

"Will" wrote:

Mama,

I am using xcel2003. Hee..

"Teethless mama" wrote:

Assuming your data A1:B5
Select A1:B5 Data Sort Sort by select Column B Decending OK
In C1: enter 1
In C2: =IF(B2<B1,C1+1,C1)
Copy from C2 to C5

The result will be 1,2,2,3,4 which are Mark, Simon, Stella, Jo, Jean


"Will" wrote:

Dear All,

I would like to rank my students but I do face prob if some of them have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Will
Ranking solution
 
I tried Valko ones and its work.. Will try urs later

"Teethless mama" wrote:

Have you try it?

"Will" wrote:

Mama,

I am using xcel2003. Hee..

"Teethless mama" wrote:

Assuming your data A1:B5
Select A1:B5 Data Sort Sort by select Column B Decending OK
In C1: enter 1
In C2: =IF(B2<B1,C1+1,C1)
Copy from C2 to C5

The result will be 1,2,2,3,4 which are Mark, Simon, Stella, Jo, Jean


"Will" wrote:

Dear All,

I would like to rank my students but I do face prob if some of them have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Will
Ranking solution
 
Valko,

I tried and its works if I have a single row..

How would I go if I have multiples row meanin:

Term 1 Term 2
Jo 56 55
Mark 98 56
Simon 88 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of them have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

T. Valko
Ranking solution
 
I tried Valko ones and its work

Mine always work! <VVBG

Biff

"Will" wrote in message
...
I tried Valko ones and its work.. Will try urs later

"Teethless mama" wrote:

Have you try it?

"Will" wrote:

Mama,

I am using xcel2003. Hee..

"Teethless mama" wrote:

Assuming your data A1:B5
Select A1:B5 Data Sort Sort by select Column B Decending OK
In C1: enter 1
In C2: =IF(B2<B1,C1+1,C1)
Copy from C2 to C5

The result will be 1,2,2,3,4 which are Mark, Simon, Stella, Jo, Jean


"Will" wrote:

Dear All,

I would like to rank my students but I do face prob if some of them
have the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better
if it show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

T. Valko
Ranking solution
 
Do you mean ranked by the total of both coulmns?

One way (still array entered):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,0))

Biff

"Will" wrote in message
...
Valko,

I tried and its works if I have a single row..

How would I go if I have multiples row meanin:

Term 1 Term 2
Jo 56 55
Mark 98 56
Simon 88 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of them
have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Will
Ranking solution
 
Valko,

no, individual.

Meaning, if the person will the highest mark is still Mark. I wan to formula
to "detect" which is the highest mark amount the whole score table.

Term 1 Term 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Do you mean ranked by the total of both coulmns?

One way (still array entered):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,0))

Biff

"Will" wrote in message
...
Valko,

I tried and its works if I have a single row..

How would I go if I have multiples row meanin:

Term 1 Term 2
Jo 56 55
Mark 98 56
Simon 88 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of them
have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

Will
Ranking solution
 
Anyone??

"Will" wrote:

Valko,

no, individual.

Meaning, if the person will the highest mark is still Mark. I wan to formula
to "detect" which is the highest mark amount the whole score table.

Term 1 Term 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Do you mean ranked by the total of both coulmns?

One way (still array entered):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,0))

Biff

"Will" wrote in message
...
Valko,

I tried and its works if I have a single row..

How would I go if I have multiples row meanin:

Term 1 Term 2
Jo 56 55
Mark 98 56
Simon 88 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of them
have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks

T. Valko
Ranking solution
 
I see that you made a new post.

Biff

"Will" wrote in message
...
Anyone??

"Will" wrote:

Valko,

no, individual.

Meaning, if the person will the highest mark is still Mark. I wan to
formula
to "detect" which is the highest mark amount the whole score table.

Term 1 Term 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Do you mean ranked by the total of both coulmns?

One way (still array entered):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6+C$2:C$6-ROW(B$2:B$6)/10^10,0))

Biff

"Will" wrote in message
...
Valko,

I tried and its works if I have a single row..

How would I go if I have multiples row meanin:

Term 1 Term 2
Jo 56 55
Mark 98 56
Simon 88 57
Stella 88 58
Jean 50 51

"T. Valko" wrote:

Try this:

Entere as an array using the key combination of CTRL,SHIFT,ENTER
(not
just
ENTER):

=INDEX(A$2:A$6,MATCH(LARGE(B$2:B$6-ROW(B$2:B$6)/10^10,ROWS($1:1)),B$2:B$6-ROW(B$2:B$6)/10^10,0))

Copy down as needed.

Biff

"Will" wrote in message
...
Dear All,

I would like to rank my students but I do face prob if some of
them
have
the
same marks

Example:

Jo 56
Mark 98
Simon 88
Stella 88
Jean 50

When I uses =large (range, 1) = Mark (tis is correct)
When I uses =large (range, 2) = Simon (tis is correct, its better
if it
show
both names)
When I uses =large (range, 3) = Simon (tis is wrong)

Pls help me... thanks


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com