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

#1 01-08-2005, 10:06 PM

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)

duane · #2 01-08-2005, 10:34 PM

see Rank function: for rank of column b

=rank(b1,b1:b100,0)

ranks highest to lowest

change ,0 to , 1 for lowest to highest

#3 01-08-2005, 11:07 PM

Hi John

Have a look at Excel's "RANK" formula
eg =RANK(B1,B1:B25,1)

--
XL2002
Regards

William

willwest22@yahoo.com

"JohnT" <anonymous@discussions.microsoft.com> wrote in message
news:15d101c4f5f7$2a234be0$a401280a@phx.gbl...
| 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 01-09-2005, 05:06 AM

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

{"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 are:

{"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)

#5 01-09-2005, 05:06 AM

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" <willwest22@yahoo.com> wrote in message
news:ufSpEif9EHA.3828@TK2MSFTNGP09.phx.gbl...
> Hi John
>
> Have a look at Excel's "RANK" formula
> eg =RANK(B1,B1:B25,1)
>
>
> --
> XL2002
> Regards
>
> William
>
> willwest22@yahoo.com
>
> "JohnT" <anonymous@discussions.microsoft.com> wrote in message
> news:15d101c4f5f7$2a234be0$a401280a@phx.gbl...
> | 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 01-09-2005, 05:06 AM

I think you missed the "etc ...", Aladin.<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41E0FE12.3040802@xs4all.nl...
> 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 Score:
>
> {"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 are:
>
> {"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)

#7 01-09-2005, 06:07 AM

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 01-09-2005, 06:07 AM

Take a look at http://www.xldynamic.com/source/xld.RANK.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

"JohnT" <anonymous@discussions.microsoft.com> wrote in message
news:15d101c4f5f7$2a234be0$a401280a@phx.gbl...
> 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)

#9 01-09-2005, 09:06 AM

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

Ronald Hooper · #10 01-12-2005, 09:52 AM

I found Aladin Akyurek's formula very helpful, but Ola (?) suggestion sounds like what I really need although I don't understand quite what needs to be done.

I need to sort a table on the fly to determine 1st, 2nd, 3rd, etc. places, but by cub scout den, ie, Tiger Cub, Wolf, Bear, Webelo. I don't need the "st" etc solution suggested by Bob Phillips. This is need for a Pinewood Derby scoresheet.

The columns would be:

Car # ........ Name ........ Den ........ Rank
1 bob bear 58
2 ron cub 17
3 dave bear 60
4 bill bear 35
5 dakota cub 55
etc

Thanks for any ideas.

Ron

PS I will also post this as a new tread.

Ronald Hooper · #11 01-12-2005, 07:53 PM

Many Thanks to Aladin Akyurek for his solution and to Henrik for correctly asking his question in a more recent thread <Rank() based on category>!!!!!

Aladin's answer was just what I needed. All I had to do was change "<" to ">" to rank from high value to low value by category. He also corrected, as I see it, the formula put forth by Ola (?) by included "+1".

Again, thanks!

Welcome to the Excel Forum

Forum Statistics