I need to rank order of finish in a round robin competition and determine ties

[pongmeister]

I'm not able to figure out how to do this but what I need to do is rank players (let's say 11 players maximum in a competition and my workbook has calculated a score from their results. They are sorted in descending order. In one column (Q in my example) I want their order of finish but with Ties reconciled ie if the top three players are tied then those would all have a value "1" and a second column (R in my example) should have a value of "T1" to indicate it is the first group of players with a tie situation.

In that example the fourth player on the list would finish in fourth place and in my example the 4th, 5th and 6th players are tied so all three would show being tied for fourth place and all three should have a "4" in column Q and a value of "T2" in column R to indicate a second group of players with a tie situation.

11 is the maximum number of players I will have in an event.

I have a worksheet in my attached workbook titled "Forum Example Sorted" and that shows the desired results in columns "Q" and "R".

The reason I want to be able to do this is to be able to apply tiebreak rules to resolve these ties. I have been trying to work formulas to handle this but have not been able to figure how to do it. Hope you can help.

Thanks

[FDibbins]

Im not sure which values you are using for the ranking, so adjust these refereces as needed.

1. If you want to apply a tie-breaker, use something like this...
=RANK(Q3,$Q$3:$Q$13,1)+COUNTIF($Q$3:Q3,Q3)-1

If you just want to add a T to the rank...
="T"&RANK(Q3,$Q$3:$Q$13,1)

[pongmeister]

Hi Ford,

I didn't have any code in my post due to not knowing how to do this function. I apologize for omitting the column I want to base the ranking on. It is column 'F'. I manually entered the values I'm looking in columns "Q" and "R" in the worksheet titled "Forum Example Sorted".

I tried your formulas but they didn't give me what I'm looking for.

Thanks for your help.

[FDibbins]

OK, thanks for the update. Will your data always be sorted like that?

[pongmeister]

No, they wouldn't be sorted originally but what I thought I could do would be to sort them first then set up a macro containing all of the formulas. So, I would have blank cells in the "Q" and "R" columns. Then after sorting them I would run the macro containing the formulas to create my desired results. That is of course if you can help me with the correct formulas to do the job, since I'm kind of spinning my wheels at this point. I don't know how to write my own macros but would use the excel macro facility to record my keystrokes.

Thanks again

[FDibbins]

I can get the ranking for you...
Q3=RANK(F3,$F$3:$F$13

and I can get the Tie Break for you...
S3=COUNTIF($Q$3:Q3,Q3)
Although if you use this, it will do the rank and Tie=break in 1 step...
=RANK(F3,$F$3:$F$13)+COUNTIF($F$3:F3,F3)-1

But I am having a hard time with your T sequence

[pongmeister]

Hi Ford,

Thanks for those two formulas - they both worked great!! I am trying to work something else out for the T sequence. I only arbitrarily picked a "T!", "T2" etc. value for the T sequences column "R" - If easier to handle I could use with just the sequence number - the "T" is not crucial.

Thanks again

[pongmeister]

Hi Ford,

I was able to work the T sequence column due to your having solved the "Q" and "S" column formulas. What I did was create 2 new columns (T AND U).

Then I marked a "T" in column "T" if the value in column "F" was equal to the previous or next row F.

Please Login or Register  to view this content.

Then set U3 to value in S3 (value is always 1)

Please Login or Register  to view this content.

Then in U3 THRU U13 i added value of '1' to previous rows column U value if current row S value is greater than 1 otherwise I used the previous rows column U value.

Please Login or Register  to view this content.

Then I concatenated the two new columns (T and U) into column R if column T had a value of "T".

Please Login or Register  to view this content.

Here are the results
COL COL COL COL COL
Q R S T U

Place Tie Player
Ind.& Tie
set# Break

1 T1 1 T 1
1 T1 2 T 1
1 T1 3 T 1
4 T2 1 T 2
4 T2 2 T 2
4 T2 3 T 2
7 T3 1 T 3
7 T3 2 T 3
9 T4 1 T 4
9 T4 2 T 4
11 1 5

This was a tricky and complicated problem and I couldn't have done it on my own. Thank so much!!

















=IF(S4>1,U3,U3+1)

[FDibbins]

Good job

However, thats why I asked if teh values will always be sorted. Try changing a value (say F8) to 18, then t doesnt work so well

The T is easy to add, just ="T"&formula
It's the "formula" thats i am having a hard time with

[pongmeister]

Yes, I agree - It always has to be sorted. I set up a macro that does the sort and then enters all the formulas. It's a rather cumbersome method but I couldn't come up with a different way.

Thank you again

[FDibbins]

OK, I think I have it, with the help from vlady - another mod. No sort needed
Put this ARRAY formula in R3 and copy down...
="T"&SUM(IF(Q3>$Q$3:$Q$13,1/COUNTIF($Q$3:Q$13,$Q$3:$Q$13)))+1

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[pongmeister]

This is a much better alternative. It only had one small (non-crucial glitch)

When a player is not tied they still get a T number. R13 has a value of "T5" but should be spaced out. I changed values
manually in column F and this occurs on all players who are not tied with any other.

I am using column R in other worksheets to load only tied players which will then give new rankings for only those players
in a tie. eg If we have 3 players tied then only the matches between those 3 tables are used to find their final ranking.

Not crucial because we would just ignore the worksheet that only loaded a single player.


Thanks for all the resolutions you gave me!!

[FDibbins]

great

You could also use this to ignore not-repeats....
=IF(COUNTIF($Q$3:$Q$13,Q3)=1,"","T"&SUM(IF(Q3>$Q$3:$Q$13,1/COUNTIF($Q$3:Q$13,$Q$3:$Q$13)))+1)

Also CSE entered

[pongmeister]

I tried that and it did ignore not-repeats but all tie groups had "T1" - I didn't know what "also CSE entered" means - Is that another person or something I should do for this command??

Thanks

[Tony Valko]

CSE means the formula is an array formula and must be entered using the key combo of CTRL + SHIFT + ENTER.

Hold down both the CTRL key and the SHIFT key then hit ENTER.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

[pongmeister]

Wow, that made all of the difference. You and Ford have solved this complex problem perfectly. Awesome!!!

Thanks Much

[Tony Valko]

You're welcome. We appreciate the feedback!

[FDibbins]

Thanks for the assist Tony

[pongmeister]

Hi Ford,

I thought this one was over, but I am encountering 2 problems. I'm attaching my workbook.

1) On worksheet named "Forum Example Sorted" with a tie group 4 (T4) but since Q9 was not tied this should be set to "T3".
2) On worksheet named "ca_g4_rr1" "I'm getting #DIV/0! message.

I must have something wrong but I haven't been able to see what it is???

Thanks.

[FDibbins]

2) On worksheet named "ca_g4_rr1" "I'm getting #DIV/0! message.

Adjust the range

[pongmeister]

I made the range adjustment to worksheet "ca_g4_rr1" and that solved the #DIV/0!" message, but I now have wrong values in col. "R" similar to my issue 1

1) On worksheet named "Forum Example Sorted" with a tie group 4 (T4) but since Q9 was not tied this should be set to "T3".
2) Now on the ca_g4_rr1 worksheet Rows 3 & 4 are tied in col. "F" but R3 shows a blank and R4 has value "T2"

This is the adjustment I made to ca_g4_rr1 column R:

Please Login or Register  to view this content.

Thanks for any help you can give on this.

[FDibbins]

Maybe I misunderstood, but from your post 12...

When a player is not tied they still get a T number. R13 has a value of "T5" but should be spaced out.

So I modified the formula to show nothing.

If you want the T value to show for non-duplicates as well, go back to the original formula...
="T"&SUM(IF(Q3>$Q$3:$Q$13,1/COUNTIF($Q$3:Q$13,$Q$3:$Q$13)))+1

[pongmeister]

Hi Ford,

No, I prefer not to have non-duplicates show the T value. The result I'm getting in worksheet ca_g4_rr1 is:


F3 = 3 Q3=1 Q3 SHOULD BE 1 R3="" R3 SHOULD BE T1
F4 = 3 Q4=3 Q4 SHOULD BE 1 R4=T2 R4 SHOULD BE T1
F5 = 2 Q5=3 Q5 SHOULD BE 2 R5=T2 R5 SHOULD BE T2
F6 = 2 Q6=5 Q6 SHOULD BE 2 R6=73 R6 SHOULD BE T3
F7 = 1 Q7=5 Q7 SHOULD BE 3 R7=T3 R7 SHOULD BE T3
F8 = 1 Q8=7 Q8 SHOULD BE 3 R8=T4 R8 SHOULD BE T3
F9 = 0 Q9=7 Q9 SHOULD BE 4 R9=T4 R9 SHOULD BE T4
F10 = 0 Q10=7 Q10 SHOULD BE 4 R10=T4 R10 SHOULD BE T4

I've attached my workbook. The first two values in col. F are equal but cell R3 show it as a duplicate. Then
it marks cell R4 with value T2.

Also please look at worksheet "ForUm Example Sorted" It gets out of sync after a non-dupe in r9 - row r8 had a correct
value of T2 then row r10 has value of T4 but r10 is in the third set of duplicates and should be T3

I don't know what to do to fix this.

Thanks so much