Forum Discussion
Create a ranking
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.
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].
(Peter, I saw your post after writing to @Nikolino - will come back on your points today - Many thanks for also trying to help).
Original ranking and results tableScores entered here
Thanks - I'm learning lots. I found that I can attach photos here, so hoping the attachments help.
Some extra (non material) info to help explain the screenshots: The columns mapping is slightly different (see table) as I've included extra columns for date, set results, points etc. Highlighted in Row 2 though are your column mappings.
I have a separate sheet (in the screenshot) which collates the results automatically and shows them in a table (points, % challenges won etc..). This is where I draw the list of players from and is already set up based on previous outcomes, so the ranking starts with a non alphabetical list. This list has more columns (this is a further question on how to update that list with these results, moving all row data up or down depending on the outcome of the matches played).
Formula Your naming My naming
Your column A B from a separate sheet named 'Table'
Your column B B: challenger Your column C E: opponent =IFS(ISBLANK(B3),"",C3>D3,$R$6,C3<D3,$R$7) Your column D F: win/loss =IF(OR(B3="", E3="", F3=""), "", IF(F3="Win", MATCH(E3, Table!$B$3:$B$18, 0), MATCH(B3, Table!$B$3:$B$18, 0))) - - Showing the suggested new position perfectly correctly. Once the ranking is updating automatically, subsequent games will start with a different ranking. Your column E J: matching positions =IF(J3="", "", IF(F3="Win", ROW(B3) + J3 - ROW(E3), IF(ISNUMBER(B3), ROW(B3), 0))) - Showing the same as your column E (matching positions), except in two rows in the example which return 0 Your column F K: ranking =IF(K3="","",IF(ISNUMBER(B3),RANK(K3,K:K,0),0))
- Showing 0's
Your column G L: dynamic ranking =IFERROR(INDEX(Table!B3:B18, MATCH(ROW(), $G$3:$G$50, 0)), "") - Not showing any data Your column H N: displaying the rank I think the screenshots will help to clarify the ranking problem, as it still exists. I haven't been clear enough.
The rank needs to be affected only by random game outcomes (no-one is forced to play a certain number of games in a set time period), so it could be that player A challenges 4 people, and no-one else challenges anyone, nor receives other challenges. The ranking therefore is the starting rank in the 'Table' sheet, together in sequence with the outcome of each match, whereby if the challenger wins, he moves above the opponent, and if he fails, there is no change to the ranking.
Thanks for all your help here.
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.
