Home |
Search |
Today's Posts |
#1
|
|||
|
|||
1st, 2nd, 3rd Place etc.....
I have an idea how to do this but it requires a series of
nested ifs and i'm sure there is a better way......in column A i have a list of names, column B is their scores and in C i would like to rank them in 1st, 2nd, 3rd etc.... any ideas???? (thanks in advance) |
#2
|
|||
|
|||
Hi John
Have a look at Excel's "RANK" formula eg =RANK(B1,B1:B25,1) -- XL2002 Regards William "JohnT" wrote in message ... | I have an idea how to do this but it requires a series of | nested ifs and i'm sure there is a better way......in | column A i have a list of names, column B is their scores | and in C i would like to rank them in 1st, 2nd, 3rd etc.... | any ideas???? | | (thanks in advance) |
#3
|
|||
|
|||
I suspect that you want to create a Top N list, with N set to 3...
Let A2:B9 house, including the labels Name and Sco {"Name","Score"; "dawn",23; "damon",23; "bob",25; "chris",22; "christine",25; "ian",32;"john",35} The foregoing just shows sample records, where each record consists of a row of two cells. In C2 enter & copy down: =RANK(B3,$B$3:$B$9)+COUNTIF($B$3:B3,B3)-1 This calculates a unique rank based on the scores. In D1 enter: 3 which indicates that you want a Top 3 list. In D2 enter: Top N which is just a label. In D3 enter & copy down: =IF(ROW()-ROW(D$3)+1<=$D$1+$E$1,INDEX($A$3:$A$9,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$9,0)),"") This builds a Top N list of names. Note that this formula refers to $E$1 that houses a formula. E1: =MAX(IF(INDEX(B3:B9,MATCH(D1,C3:C9,0))=B3:B9,C3:C9 ))-D1 which must be confirmed with control+shift+enter instead of just usual enter. This formula calculates the ties of the Nth (3rd) value itself. In E2 enter: Associated Score which is just a label. In E3 enter & copy down: =IF(D3<"",INDEX($B$3:$B$9,MATCH(ROW()-ROW(E$3)+1,$C$3:$C$9,0)),"") The results are for the sample data a {"john",35; "ian",32; "bob",25; "christine",25} JohnT wrote: I have an idea how to do this but it requires a series of nested ifs and i'm sure there is a better way......in column A i have a list of names, column B is their scores and in C i would like to rank them in 1st, 2nd, 3rd etc.... any ideas???? (thanks in advance) |
#4
|
|||
|
|||
Be careful !
That third argument ( ,1 ) *may* not be necessary. =RANK(B1,$B$1:$B$25) Will rank the *highest* score as number "1". =RANK(B1,$B$1:$B$25,1) Will rank the *lowest* score as number "1" (golf). -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "William" wrote in message ... Hi John Have a look at Excel's "RANK" formula eg =RANK(B1,B1:B25,1) -- XL2002 Regards William "JohnT" wrote in message ... | I have an idea how to do this but it requires a series of | nested ifs and i'm sure there is a better way......in | column A i have a list of names, column B is their scores | and in C i would like to rank them in 1st, 2nd, 3rd etc.... | any ideas???? | | (thanks in advance) |
#5
|
|||
|
|||
I think you missed the "etc ...", Aladin.<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... I suspect that you want to create a Top N list, with N set to 3... Let A2:B9 house, including the labels Name and Sco {"Name","Score"; "dawn",23; "damon",23; "bob",25; "chris",22; "christine",25; "ian",32;"john",35} The foregoing just shows sample records, where each record consists of a row of two cells. In C2 enter & copy down: =RANK(B3,$B$3:$B$9)+COUNTIF($B$3:B3,B3)-1 This calculates a unique rank based on the scores. In D1 enter: 3 which indicates that you want a Top 3 list. In D2 enter: Top N which is just a label. In D3 enter & copy down: =IF(ROW()-ROW(D$3)+1<=$D$1+$E$1,INDEX($A$3:$A$9,MATCH(ROW()-ROW(D$3)+1,$C$3: $C$9,0)),"") This builds a Top N list of names. Note that this formula refers to $E$1 that houses a formula. E1: =MAX(IF(INDEX(B3:B9,MATCH(D1,C3:C9,0))=B3:B9,C3:C9 ))-D1 which must be confirmed with control+shift+enter instead of just usual enter. This formula calculates the ties of the Nth (3rd) value itself. In E2 enter: Associated Score which is just a label. In E3 enter & copy down: =IF(D3<"",INDEX($B$3:$B$9,MATCH(ROW()-ROW(E$3)+1,$C$3:$C$9,0)),"") The results are for the sample data a {"john",35; "ian",32; "bob",25; "christine",25} JohnT wrote: I have an idea how to do this but it requires a series of nested ifs and i'm sure there is a better way......in column A i have a list of names, column B is their scores and in C i would like to rank them in 1st, 2nd, 3rd etc.... any ideas???? (thanks in advance) |
#6
|
|||
|
|||
Take a look at http://www.xldynamic.com/source/xld.RANK.html
-- HTH RP (remove nothere from the email address if mailing direct) "JohnT" wrote in message ... I have an idea how to do this but it requires a series of nested ifs and i'm sure there is a better way......in column A i have a list of names, column B is their scores and in C i would like to rank them in 1st, 2nd, 3rd etc.... any ideas???? (thanks in advance) |
#7
|
|||
|
|||
I don't think so...
In cell D1, the N of Top N can be set to any desired value the data admits. <bg Ragdyer wrote: I think you missed the "etc ...", Aladin.<bg |
#8
|
|||
|
|||
Hi,
IF your wish to separate by Gender (Male/Female). Name....Gender....Value....Rank D4: =SUM((B4=$B$4:$B$100)*(C4$C$4:$C$100)) Confirm the formula with Ctrl+Shift+Enter not just Enter. Conditional Formatting can give you two colors. (To be incl. in the suggestions above.) Ola |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
How do I create a macro button and place anywhere on my sheet? | Excel Discussion (Misc queries) | |||
Can I place visible text in cell that won't print | Excel Discussion (Misc queries) | |||
Req Formula to place 1 into next columns | Excel Worksheet Functions | |||
Req Formula to place 1 into next columns | Excel Worksheet Functions |