| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need a sorting solution | Excel Worksheet Functions | |||
| IF statement? Or another solution? | Excel Discussion (Misc queries) | |||
| Trying to generate a solution | Excel Discussion (Misc queries) | |||
| please find a solution | Excel Discussion (Misc queries) | |||
| Solution please | Excel Discussion (Misc queries) | |||
Hybrid Mode
