I need help to rank ties (can't get it to work...)

[nosskire]

My sheet has the following appearance:

Column A: Numbers in order from 1 to 23 (a total of 23 rows that is)
Column B: Names of persons (23 different persons, one per row)
Column C: Sales X (Scores from 1 to 10 (only to 10 persons)), where 10 is the highest. (One person get 1 points, one get 2 points, one get 3 points and so on up to 10 points for the winner)
Column D: Sales Y (Scores from 1 to 10 (only to 10 persons)), where 10 is the highest. (One person get 1 points, one get 2 points, one get 3 points and so on up to 10 points for the winner)
Column E: Total sum of column C and D
Column F: The rank of column E

NOW, here comes the problem. I need to have a "Top 10 with NO ties". If there is a tie based on the sum (of C and D) it's the sales from "Sales Y" (column D) that should rank the person higher.

What kind of formula should I use to get this to work?

Thankful for any kind of help!

Best regards
Emil

 

[barry houdini]

Try this formula

=RANK(E$1:E$23,E1)+SUMPRODUCT((E$1:E$23=E1)*(D$1:D$23>D1))

 

[AlphaFrog]

When you sum C and D you can include a tie breker as a decmal value based on column D.

Example:
=C1+D1+(D1/100)

So if C1 is say 5 and D1 is say 7, the result will be 5+7+(7/100) = 12.07

If the C2 was 7 and D2 was 5, the result will be 7+5+(5/100) = 12.05

12.07 would rank higher than 12.05

 

[nosskire]

Thank you AlphaFrog, that really helped me. Now it does exactly as I like it to do!

Now I have another thing I would like to get helped with:

I would like an extra column right after the total ranking where the ranking number is transformed to a score in this new column.

For instance:
If I rank first my score turns to 10, if I'm ranked second my score turns to 9, and so on until the ranking is tenth - then my score is 1. All others are zero.
How do I do that?

Br
Emil

 

[dave3009]

Just another suggestion

=RANK(E$1:E$23,E1)+COUNTIF(E$1:E1, E1)-1

 

[AlphaFrog]

For instance:
If I rank first my score turns to 10, if I'm ranked second my score turns to 9, and so on until the ranking is tenth - then my score is 1. All others are zero.
How do I do that?

Try something like this...
=MAX(0,11-RANK(E1,$E$1:$E$23))

 

[nosskire]

Can you develop it a bit more? I don't get it, and it didn't work if I used that formula.

 

[AlphaFrog]

With this formula...

=MAX(0,11-RANK(E1,$E$1:$E$23))

RANK(E1,$E$1:$E$23) returns the rank of E1. If E1 was the largest, that would rank as 1

11-1 = 10 score

Max(0,10) returns 10

The max function is to prevent negative scores.

If E1 ranked at say 12

11-12 = -1

Max(0,-1) = 0 score

 

[Pikeboy]

Alpha, how do you handle ties? For example, I'm ranking 8 teams' home runs for fantasy baseball and two teams are tied:

Team, HR total, rank

Team A, 15, 8
Team B, 13, 6.5
Team C, 13, 6.5
etc, etc, etc for remainder of teams in league.

I cannot seem to get the 6.5 value using the below calculation:

=COUNT($C$18:$C$25)-(RANK(C18, $C$18:$C$25)+COUNTIF($C$18:C25, C18)-1)+1

Thanks in advance,

RC

 

[AlphaFrog]

Alpha, how do you handle ties?

It depends on what you want to use as a tie breaker criteria.

Perhaps this will help.
Excel RANK Function Examples