Ranking Help

**BuzzT** · 02-04-2011, 03:57 PM

Hi Everyone,

I am trying to assign a point score based on a ranking of pecentages in two columns. In the first percentage column (column B), a point score of 1 to 6 is assigned based on the highest percentage (6 for highest, 5 for second highest, etc.). I seem to have this one solved in column C.

The problem is ranking the next percentage column (column D), which needs to assign points in reverse ranking (6 for lowest, 5 for next lowest, etc.) in Column E. Again, to some degree I have acheived this, however as you will see in the attached file, when there are duplicate values, it assigns the lower possible point score, instead of the next one in the sequence (i.e. 6, 5, 4, 2, 2, 1). What I need is for it to do 6, 5, 4, 3, 3, 2 (skipping 1 altogether).

I hope this is understandable as it appears confusing even to me and I am at a loss right now.

**TechRetard** · 02-04-2011, 04:00 PM

sorry my solution i just posted was wrong..1 sec

**BuzzT** · 02-04-2011, 04:05 PM

That's close, but now I just have a sequential ranking (1-6) where the first of the duplicates automatically is ranked higher than the second which skews the results.

**TechRetard** · 02-04-2011, 04:10 PM

is this it?

=COUNTA($D$3:$D$8)+1-RANK(D3,$D$3:$D$8,1)

**BuzzT** · 02-04-2011, 04:13 PM

Wow! That was fast! It now gives 6,5,4,3,3,1 (leaves out the 2, but this will work as that percentage would have received a 1 if there had been no duplicates anyway).

Thanks a bunch!