Ranking issue, how to eliminate duplicates?

[chaz4070]

Hi everyone,

I have data that I am trying to rank agents on:

http://gallery.me.com/chaz4070#100054/Screen shot 2010-01-04 at 10.13.25 AM&bgcolor=black

I am trying to break out each metric like so:

http://gallery.me.com/chaz4070#100054/Screen shot 2010-01-04 at 10.15.26 AM&bgcolor=black

I am just copying over each category and then applying the "Rank" function in the following manner "=RANK(B3,$B$3:$B$23,0)" to get the rank in the third row.

I am then using "=OFFSET(A$3,MATCH(SMALL(R$3:R$23,ROW()-ROW(R$3)+1),R$3:R$23,0)-1,0)" in the next columns to sort in the Ranked order.

I am running into trouble when there are duplicate ranks... for example:

http://gallery.me.com/chaz4070#100054/Screen shot 2010-01-04 at 10.15.43 AM&bgcolor=black

As you can see, there are multiple people with the same value (100%) which makes them all #1... Unfortunately, my OFFSET/MATCH then returns the the same value for all #1s instead of listing all #1's names.

Hope this is making sense... and thanks for any assistance...

I did find this post (http://www.mrexcel.com/forum/showthread.php?t=437674&highlight=rank) which definitely seems relevant... especially the bit about accounting for dupes but I wasn't sure how to incorporate it into my formula.

Thanks again,

Charlie

 

[sunnyland]

Hello,

Just to let you know that the link to your sample data required a username and password, this is may be why you have not yet an answer.

See if you can upload them as public, so people can access them easily.

 

[jbeaucaire]

When you have duplicate ranked values, you need to decide how you're going to TIEBREAK them. Here's a link to a sample sheet where I demonstrate a way to use:

1) another set of values to break the ties
2) several other sets of values to break the ties
3) use "position" in the chart to break the tie (the higher you are ON the list, the better rank goes to you)

The File directly

The Folder with many files]

 

[barry houdini]

Perhaps Charlie has given up on an answer by now.....but if not then you can get unique ranks with this formula

=RANK(B3,$B$3:$B$23,0)+COUNTIF(B$3:B3,B3)-1

in row 3 copied down