RANK Data to Break Ties

[Busymanjohn]

Hi guys, I've been asked to create a file for a contest. There are five judges giving nine contestants scores. It's simple enough to count the scores and rank the results based on the scores, but having a little difficulty with tied places. I am using =IF(COUNTIF to break the ties, which works fine, but is there a way to show the true rank for rows 14 and 17 in the attached file? Right now, with the break I am using returns results for those 2 rows as 9.07 and 9.09 respectively, whereas the true result is 8 and 9.


https://rapidshare.com/files/460806134/Rank_Ties.xls

 

[Hui]

Busymanjohn


Most people add a small value to the original scores before ranking

So your score/value formula becomes

=Formula + row()/1000000


in your example you can simplify X9 to:

=M9*12+N9*9+O9*8+P9*7+Q9*6+R9*5+S9*4+T9*3+U9*2+V9+ROW()/1000

and Y9 to:

=RANK(X9,Datarange)

then delete Columns Z & AA


Make sure you format Column X to have no decimals


In some sports they have rules and that can be used to conditionally add small values


Also note that you don't need to use a Sum() in your formula =M9*12+

Excel is very good at following mathematical conventions of BODMAS or BIMDAS (depending on which school you went to) and so even the brackets can be left out in this case

 

[Busymanjohn]

Hi Hui, thanks for the feedback, appreciate it. In my example though, with the changes made as per your post, there is a tie break ( rows 14 and 17 ) both with scores of 14 and based on the judges scores, row 14 should be in 8th place and not 9th. I guess I could sort the data based on column X which gives the desired result, but would have to try that through various scenarios ( i.e. more than one tied place )to see if the sort would still work.

 

[Hui]

Did you copy those formulas down ?

as I get:

[pre]

Score  Rank
22.009	7
40.01	3
24.011	6
53.012	1
35.013	5
14.014	9
42.015	2
36.016	4
14.017	8

[/pre]

as scores


If you round Col X to 0 Decimals it will show up as 17 but have hidden decimals which are used for sorting and ranking


Do you have other rules which can be used to seperate a tie?

 

[Busymanjohn]

Hi Hui, yes, data is copied down ,, and I get the same result as you ,, if you look at the results, 14.014 should be ranked 8th and 14.017 should be ranked 9th.

 

[Hui]

You data is ranked highest to lowest

14.017 is higher than 14.014 and so should be 8th


why should line 14.014 be higher than 14.017 based on what rule?

 

[Busymanjohn]

Hi Hui, understand the way the rank is working, maybe I need to look at the scoring system instead. The reason I say that 14.014 should be higher in rank to 14.017 is that if we look at the judges placings, the contestant with a score of 14.014 has 2x 9th placings while the contestant with 14.017 has 3x 9th placings, therefore the contestant with 2x 9th placings must be ranked higher than a contestant with 3x 9th placings.

 

[Hui]

You could change X9 to something like this

=M9*12+N9*9+O9*8+P9*7+Q9*6+R9*5+S9*4+T9*3+U9*2+V9+1/IF(U9>0,U9,1)+ROW()/1000000

where the 1/IF(U9>0,U9,1) bit adds the inverse of the Score in Score 9

Copy down


or


=M9*12+N9*9+O9*8+P9*7+Q9*6+R9*5+S9*4+T9*3+U9*2+V9+1/IF(OFFSET(X9,,-$X$7)>0,OFFSET(X9,,-$X$7),1)+ROW()/1000000


where you put a value of 3 into X7

Copy X9 down


The value in X7 is the offset back to the column you want to use as the decider, in this case Col U which is 3 Columns back