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 5
The formula in cell F3 is:
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:
This last row formula could be modified to:
Hope that this is of interest to you.