The problem to be resolved is that neither the RANK command, nor the formula in column G are designed to recognise and avoid a duplicate values.
Both ROC and Fod both scored 17, but there is nothing to distinguish between them.
Thankfully Prashanth at infospired.com recognised the underlying problem and solved it in "Flexible Array Formula to Rank Without Duplicates in Google Sheets".
Here's a link to my worked spreadsheet.
Here's a screenshot of Fergal's data versus my version (using the infospired solution).
infospired created a new formula to create a "Rank without Duplicates". This is the formula in the screenshot:
This is a complicated formula, but becomes easier to understand when it is broken into its component pieces. There are three steps:
- produce the ranking.
There are three steps
1 - Get the column of raw scores (as-is)
2 - Create a new column consisting of the numbers 1 to ten (inclusive), and assign one number to each score. Note this isn't ranking, it's simply assigning numbers to each score with no duplicates. The numbers are created using
ROW with this code row(D19:D28)-18. "row(D19:D28)" would create the numbers 19 to 28 but there is a second element "minus 18". This has the effect of deducting 18 from each row number; so instead of creating number 19, it creates number one (nineteen minus eighteen); two instead of twenty; three instead of twenty one; four instead of twenty two and so on.
3 - User
SORT to sort the columns by score descending.
Sort #2 -
This also consists of three steps
1 - Take the columns from sort#1
2 - Create a new column consisting of the numbers 1 to ten (inclusive), and assign one number to row from Sort#1. The same method applies as in Sort#1. The ROW command generates row numbers, but "minus 18" reduces the actual sequence to numbers one to ten inclusive.
3 - Sort the output by the number column assigned in Sort#1-Step#2; and sort the column ascending.
Produce the ranking
This step uses the
ARRAY_CONSTRAIN command . The output is an array.
1 - The input is the results from Sort#2-Step3.
2 - Create an array up to 9^9 rows long. This is a LOT; scope for over 387 million rows.
3 - 1 column wide. This is the column assigned in Sort#2. This number is now a rank, but there are no duplicate values.
The final table can be created using VLOOKUP commands for both Name and Score. Base the lookup on the Rank, and use the Rank, Name and Score table as data to be interrogated.