Microsoft Excel 2007

I understand the rank formula and i have that in my worksheet. What I am trying to do is break the tie using a different column. is there a way to do this??? Thanks, G~

Please explain what you mean by " break the tie using a different column".If you could give us some sample data to work with, that might help.

ok....so I mad a worksheet for gymnastics that has a column with the event score in it next to that column is the rank. then the next column has the 2nd event score then the rank, then the next column has the 3rd event score then the rank, then the next coulmn has the 4th event score then the rank. then the next column has their AA Score (which adds up each event score). WHen ranking each event right now if there is a tie it gives them the same rank. What I want it to do is break the tie breaker rank based on those girls AA Score. I hope this makes sense.... Thanks G~

Just so I'm clear about your requirements... Is the AA score for each girl the sum of that girl's scores for all 4 events?

If so, are you asking to break (for example) an Event 1 tie with the overall (AA) scores for the girls that tied?

In other words, if Heather and Megan both scored an 8.5 in Event 1 and Heather had an 4 event total of 30 while Megan had a 4 event total of 31, then Megan would rank higher in Event 1 than Heather, right? Kind of a retroactive ranking after the entire competition if over?

What if the overall scores are tied?

What if Heather gets injured in Event 3 and doesn't complete her 4 events? Does that mean she will be ranked lower in Event 1 since her overall score would most likely be lower than Megan's?

Yes, sorry using gymnastics terms. AA(all around) is the total of all four events. But you have the idea for the formula I am trying to figure out. If the girl didn't compete the event they would have the lowest ranking. As far as them tying for an event score and tying in their AA....it would be extremely rare for this to happen becauase gymnastics scores go out to the thousandths place. Especially at the level these girls are at. Thanks, G~

Hi, Here is a way to break a rank tie, based on the values in a different (in this case the AA) column.

The sample results below can be repeated with additional columns for Test 2 Rank 2 and Post-rank 2, as many times as required.

A B C D E F 2 Name AA Rank AA Test 1 Rank 1 Post-rank 1 3 Person #1 14.80 10 1.60 9 10 4 Person #2 28.70 9 1.60 9 9 5 Person #3 40.70 6 12.40 2 2 6 Person #4 35.10 7 12.40 2 3 7 Person #5 41.30 5 5.20 8 8 8 Person #6 52.60 1 20.00 1 1 9 Person #7 47.30 3 10.40 7 7 10 Person #8 45.50 4 11.80 4 4 11 Person #9 31.20 8 11.10 6 6 12 Person #10 49.00 2 11.30 5 5The formula in cell F3 is:

=IF(COUNTIF(E$3:E$12,E3)>1,IF(VLOOKUP($A3,$A$3:$B$12,2,FALSE) >IF(ISERROR(VLOOKUP(E3,E2:E$3,1,FALSE)),OFFSET($B3,MATCH(E3,E4:E$12,0),0), OFFSET($B2,MATCH(E3,E2:E$3,0)-1,0)),E3,E3+1),E3)

Note: All formulas have been split onto three lines for ease of viewing, and need to be 'reassembled' as single lines for pasting into cells.This formula can be dragged down the rest of the rows in column F. It can also be copied and pasted to say column I, for a Post-rank 2 column (Col. G Test 2, col H Rank 2).

This formula first tests for a second rank equal to the one in question using the COUNTIF function.

If there are no duplicates of the rank, the rank is simply copied into the Post-rank column.

If there is a duplicate rank, the IF(VLOOKUP finds the individuals AA score using VLOOKUP on their name in column A and returns their AA score from column B and then compares it to the AA score of the other person with the same rank in that Test.To find the AA score for that other person the IF(ISERROR(VLOOKUP tests if the same rank is above it in the table. This is because there is no easy way to identify which row contains the 'other' rank that is the same.

If there is no same rank above it, the same rank must be below it! Depending on whether the same rank is above or below, one or other of the OFFSET ... MATCH functions runs.

MATCH locates the row of the same rank, either above or below this individual.

OFFSET uses a row offset from the MATCH function to find the cell containing the 'other' AA score. OFFSET returns the 'other' AA score to be compared with the individuals AA score, and the second IF function is either true or false resulting in the original rank being retained, or if the 'other' person with the same rank had a higher AA score, the rank is increased by 1.

This formula will not work for more than two equal rankings. Three individuals with the same rank in one test will give erroneous results.

A blank row should be left under the table of results, unless a manual adjustment is made to the formulas in the last row.

F12 contains the following after dragging it down:=IF(COUNTIF(E$3:E$12,E12)>1,IF(VLOOKUP($A12,$A$3:$B$12,2,FALSE) >IF(ISERROR(VLOOKUP(E12,E$3:E11,1,FALSE)),OFFSET($B12,MATCH(E12,E$12:E13,0),0), OFFSET($B11,MATCH(E12,E$3:E11,0)-1,0)),E12,E12+1),E12)

This last row formula could be modified to:=IF(COUNTIF(E$3:E$12,E12)>1,IF(VLOOKUP($A12,$A$3:$B$12,2,FALSE) >IF(ISERROR(VLOOKUP(E12,E$3:E11,1,FALSE)),OFFSET($B12,MATCH(E12,E$12:E12,0),0), OFFSET($B11,MATCH(E12,E$3:E11,0)-1,0)),E12,E12+1),E12)Hope that this is of interest to you.

Regards

This is just a start, but as written it will break the ties for Event 1 based on the AA scores, regardless of how many participants have the same Event 1 score. To make it work for all 4 events, certain sections of the code would simply need to be replicated, with some Range address changes.

One Disclaimer: it will

notdeal with situations where some participants are tied with one score and others are tied with a different score.In other words it will work for 1, 2, 3, 1, 4 and 1, 1, 3, 1, 4 but not 1, 2, 2, 1, 4.

To use the code as written, the worksheet must be set up as follows. Obviously it could be adapted for other set-ups, but I had to start somewhere...

No headers row, all data starts in Row 1.

Event 1 scores are in Column A

Original Event 1 Rank Formula in Column B

Column C is blankEvent 2 scores are in Column D

Original Event 2 Rank Formula in Column E

Column F is blankEvent 3 scores are in Column G

etc.This will put the AA scores in Column M.

After the code is run, Column C should be populated with the correct rankings for Event 1.

The process:- Check each rank in Column B to see if it has a matching value.

- If not, the rank is fine, so place it in Column C

- If the value is a tie, put the address of the tie and the corresponding AA score in a VBA array.

- Once the array is populated with the addresses of all of the ties and their AA scores, sort the array in descending order based on the AA score. The addresses will tag along with the sorted scores.

- Once the array is sorted, loop through it, and increment the rank for each tie based on how many times the code went through the loop. Since the addresses of the ties are in the order of the descending AA scores, each time the code goes through the loop, it increases the result of the spreadsheet's Rank formula for the next address in the array. The rank corresponding to the highest AA score in the array has zero added to it, the next gets +1, the next gets +2, etc.I can't take credit for the Bubble Sort section of code. I merely modified what I found

hereOption Base 1 Sub TieBreak() Dim myMatch() 'Determine How many ties there are (Event 1) For Each cell In Range("B1:B7") If Application.WorksheetFunction.CountIf(Range("B1:B7"), cell) > 1 _ Then myTie = myTie + 1 Next ' 'Set up the 2 dimentional array based on the number of ties. ReDim myMatch(1 To myTie, 2) ' 'Fill the array with the cell address of the ties and the corresponding AA scores myTie = 0 For Each cell In Range("B1:B7") 'If the current value is not a tie, then move it's rank to Column C If Not Application.WorksheetFunction.CountIf(Range("B1:B7"), cell) > 1 Then Range("C" & cell.Row) = cell 'If the current value is tied with another value then put it's address 'and the corresponding AA score in the array Else: myTie = myTie + 1 'Put AA score in Array, Column 1 myMatch(myTie, 1) = cell.Offset(0, 11) 'Put Address of Tie in Array, Column 2 myMatch(myTie, 2) = cell.Address End If Next ' 'Bubble Sort the array based on Column 1, putting the AA scores in descending order. 'The addresses of the ties will be "sorted" along with the AA scores ' For i = LBound(myMatch, 1) To UBound(myMatch, 1) - 1 For j = LBound(myMatch, 1) To UBound(myMatch, 1) - 1 Condition1 = myMatch(j, 1) < myMatch(j + 1, 1) If Condition1 Then For y = LBound(myMatch, 2) To UBound(myMatch, 2) t = myMatch(j, y) myMatch(j, y) = myMatch(j + 1, y) myMatch(j + 1, y) = t Next y End If Next Next ' 'Using the sorted array, loop through the addresses 'from the lowest AA score to the highest ' For newRank = 1 To myTie 'Using the address of the Tie, increment the original rank 'with the addRank value. addRank starts at 0, so the Tie with the highest AA score 'retains it's original Rank. As the AA scores get lower, the original rank value is 'increased (a lower ranking) based on how many times we've gone through the loop. Range(myMatch(newRank, 2)).Offset(0, 1) = Range(myMatch(newRank, 2)) + addRank addRank = addRank + 1 Next End Sub

yeah...I plugged it in....and that is the problem if there are multiple ties....UGH! So frustrated......

re: " that is the problem if there are multiple ties"Who's solution are you referring to? - Humar's formula or my VBA code?

Mine works for multiple ties of the same value but not multiple ties of multiple values.

Which problem do you still have?

Hi, Here is a simple way to break the rank ties and it works with multiple ties of both the same rank and multiple ranks, so it works for this:

Prelim Rank Final rank 9 10 9 9 2 3 2 4 8 8 1 1 7 7 2 2 6 6 5 5The solution requires an additional column, which is shown below as 'Inter'. Once the table has been completed and works, the 'Inter' columns can be dragged out of the table and onto part of the spreadsheet that is not visible, or the 'Inter' columns could be hidden.

Here is the data and results:

A B C D E F G 2 Name AA Rank Test 1 Prelim Inter Post AA rank 1 rank 1 3 Person #1 14.80 10 1.60 9 1.6015 10 4 Person #2 28.70 9 1.60 9 1.6029 9 5 Person #3 40.70 6 12.40 2 12.4041 3 6 Person #4 35.10 7 12.40 2 12.4035 4 7 Person #5 41.30 5 5.20 8 5.2000 8 8 Person #6 52.60 1 20.00 1 20.0000 1 9 Person #7 47.30 3 10.40 7 10.4000 7 10 Person #8 45.50 4 12.40 2 12.4046 2 11 Person #9 31.20 8 11.10 6 11.1000 6 12 Person #10 49.00 2 11.30 5 11.3000 5

The formula in F3 is:=IF(COUNTIF($D$3:$D$12,D3)>1,D3+B3/10000,D3)

The formula in G3 is:=RANK(F3,$F$3:$F$12,0)The function works by adding a small value to tied scores. The small value is 1/10000th of the AA score.

I formatted the 'Inter' column to show 4 decimal places so that you can see how this works.

Column G now ranks these adjusted scores.Regards

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History