Forum Discussion
Solved
Create a ranking
In a sports ladder (just a list of players, not a league, nor tournament) a player (the challenger) can challenge another (the opponent) who is up to three places above them. If they win, they move a...
You may still be reeling so I have had a first cut at incorporating your data
Dragging the corner of the matches table down should incorporated additional matches as would adding further data. The stats columns could be added as required.
That's really helpful, so thankyou NikolinoDE.
This is exactly what I needed - it's not there yet though.
The first step (1) worked a treat, enabling the new position to be determined.
Step 2 has produced the same list as step 1, so I'm unsure if that's me making a mistake or not.
Step 3 is working as a ranking, but ranks based on the players results (If someone hasn't played though, they are not included).
I think that the only change to rank comes when a challenger wins a game. A lost challenge changes nothing, so the ranking isn't based on a list of results, but a single result being applied back into the original ranked list of players.
[When I did use the final step, it didn't show the right order, and has a couple of gaps, so I've not been able to implement that.
I don't know how to attach a file here, so have moved it to an online version for easier understanding if that's OK? https://docs.google.com/spreadsheets/d/12wAQ_kJB9vCRwT8r7UdOf0QCzCbHzPiroJ6AUHzjcSQ/edit#gid=1284103795
This is exactly what I needed - it's not there yet though.
The first step (1) worked a treat, enabling the new position to be determined.
Step 2 has produced the same list as step 1, so I'm unsure if that's me making a mistake or not.
Step 3 is working as a ranking, but ranks based on the players results (If someone hasn't played though, they are not included).
I think that the only change to rank comes when a challenger wins a game. A lost challenge changes nothing, so the ranking isn't based on a list of results, but a single result being applied back into the original ranked list of players.
[When I did use the final step, it didn't show the right order, and has a couple of gaps, so I've not been able to implement that.
I don't know how to attach a file here, so have moved it to an online version for easier understanding if that's OK? https://docs.google.com/spreadsheets/d/12wAQ_kJB9vCRwT8r7UdOf0QCzCbHzPiroJ6AUHzjcSQ/edit#gid=1284103795
The formulas should actually work in Excel. As for Google Sheets, which was not clearly defined from the start, my knowledge is limited as to which Excel function works on Google Sheets. The link to the file requires data for access, which I do not allow.
Here is a breakdown of the formulas and their expected outcomes:
Step 1: Identify the Matched Rows
- Column E: Matched Rows
- Formula: =IF(OR(B2="", C2="", D2=""), "", IF(D2="Win", MATCH(C2, $A$2:$A$100, 0), MATCH(B2, $A$2:$A$100, 0)))
- Purpose: This formula determines the row number of the opponent or challenger based on the match outcome. If there's no match, it leaves the cell blank.
- Expected outcome: For a win, the formula should return the row number of the opponent. For a loss, it should return the row number of the challenger. If no match is found, it should return an empty cell.
Step 2: Determine the New Positions
- Column F: New Positions
- Formula: =IF(E2="", "", IF(D2="Win", ROW(B2) + E2 - ROW(C2), ROW(C2) + E2 - ROW(B2)))
- Purpose: This formula calculates the new position for the winner based on their original position, the opponent's position, and the match outcome.
- Expected outcome: For a win, the formula should return the new position for the winner, which is their original position plus the difference in their positions with the opponent.
Step 3: Implement the Dynamic Ranking
- Column G: Dynamic Ranking
- Formula: =IF(F2="", "", RANK(F2, F:F, 0))
- Purpose: This formula applies the RANK function to create a dynamic ranking based on the new positions determined in Step 2.
- Expected outcome: The ranking should reflect the updated positions based on the matches played.
Step 4: Display the Ranked Ladder
- Column H: Ranked Ladder
- Formula: =IFERROR(INDEX($A$2:$A$100, MATCH(ROW(), $G$2:$G$100, 0)), "")
- Purpose: This formula utilizes the INDEX and MATCH functions to display the ranked ladder based on the dynamic ranking in Column G.
- Expected outcome: The ranked ladder should show the players in their updated positions based on the matches played.
- Thank you for the extra detail - I will re-run in Excel. (Just using Google sheets to share with you for greater context)
- Hi NikolinoDE,
Luckily the formulae work in both set ups.
Going back to my earlier points, Step 3 is working as a ranking, but ranks based on the players results. As this is a ladder, some will not play within a given period, and others will play multiple times, so not all players will be included, and so the table cannot be a ranking of the resulting (there is a starting hierarchy).
Ranking changes when a challenger wins a game. A lost challenge changes nothing, so the ranking isn't based on a list of results, but a single result being applied back into the original ranked list of players.
[When I did use the final step, it didn't show the right order, and has a couple of gaps, so I've not been able to implement that].You're correct that the ranking in Step 3 is based on the players' results. This is because the RANK function is being used to determine the ranking, and the RANK function only takes into account the values in the range specified.
To address the issue of incomplete rankings for players who haven't played, you can modify the formula in Step 3 to include a condition that checks if the player has played a match. If the player hasn't played a match, the formula can return a placeholder value, such as "0" or "N/A", instead of including them in the ranking.
Here's an example of how to modify the formula:
=IF(E2="", "", IF(D2="Win", ROW(B2) + E2 - ROW(C2), IF(ISNUMBER(B2), ROW(B2), 0)))
This formula introduces the ISNUMBER function to check if the value in cell B2 is a number (indicating that the player has played a match) or not. If it's not a number, it returns a placeholder value instead of including the player in the ranking.
Regarding the gaps in the ranking, it's likely because the ranking is not being updated correctly after a match is played. To ensure the ranking is updated properly, you can modify the formulas in Steps 3 and 4 to recalculate the ranking after each match.
Here's an example of how to modify the formulas:
Step 3: Update Dynamic Ranking
=IF(F2="", "", IF(ISNUMBER(B2), RANK(F2, F:F, 0), 0)))
This formula checks if the value in cell B2 is a number (indicating that the player has played a match) before calculating the ranking. If it's not a number, it returns 0 to indicate that the ranking should not be updated for that player.
Step 4: Update Ranked Ladder
=IFERROR(INDEX($A$2:$A$100, MATCH(ROW(), $G$2:$G$100, 0)), "")
This formula updates the ranked ladder based on the updated dynamic ranking in Column G.
By incorporating these modifications, you can ensure that the ranking is updated correctly after each match, including for players who haven't played, and removes any gaps in the ranking.