Rank 3 columns

[neil40]

Hi

I have a list of competitors, and would like to create a ranked table, based on their podium finishes (1st, 2nd and 3rd)

Example
Name---1st's---2nd's---3rd's
Bob--------6--------2---------1
Fred-------4--------2--------2
Jim---------4--------1--------2
Alan-------0---------0-------1

So, the criteria would be that 1st places have precedence, then 2nd's then 3rd's

I'd welcome some advice on how best to tackle this please, and 'ideally' to exclude those with no podium finishes

Many thanks
Neil

[JBeaucaire]

Put this formula in E2 and copy down to create a unique "key":

=(B2&"."&C2&D2)*1

Put this formula in F2 and copy down for the ranking:

=RANK(E2,$E$2:$E$5)


===========
'RANK-TIEBREAKING - here's a bunch more techniques and methods for dealing with tie-break ranking:

Ranking Examples

(the file you want is Rank-TieBreaking.xls)

http://www.excelforum.com/excel-prog...-tables.html#2
http://www.excelforum.com/attachment...iebreaking.xls

[neil40]

I quite like this solution *BUT*

In the scenario where for example there are two entries as follows

4-9-2
4-10-2

....the ranking is incorrect and 4-9-2 gets ranked above 4-10-2

[protonLeah]

Does

=IF(B2=0,"00",TEXT(B2,"0#")) & IF(C2=0,"00",TEXT(C2,"0#"))& IF(D2=0,"00",TEXT(D2,"0#"))

in E2 and copied down with the table sorted on E d(decending) work?

My sample:

Name	1st's	2nd's	3rd's	sort key
 Alan	10	0	1	100001
 Bob	6	10	10	061010
 Fred	4	10	2	041002
 Jim	4	9	2	040902

[shg]

JB's approach was fine for the data you provided.

      -B-- --C-- --D-- --E-- --F--- ---------------------G---------------------
  2   Name 1st's 2nd's 3rd's Helper                                            
  3   Bob      6     2     1  60201 F3 and down: =SUMPRODUCT(C3:E3*100^{2,1,0})
  4   Fred     4     2     2  40202                                            
  5   Jim      4     1     2  40102                                            
  6   Alan     0     0     1      1

That approach will break if someone has over 99 in any category.

[neil40]

shg - this is the one that works. Thanks.

I seriously doubt any event will get near 99!