ExcelBanter - 1st, 2nd, 3rd Place etc.....

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - 1st, 2nd, 3rd Place etc..... (https://www.excelbanter.com/excel-worksheet-functions/8904-1st-2nd-3rd-place-etc.html)

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)

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)

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)

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)

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)

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

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