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.
Creating a dynamic ranking system based on match outcomes without a points-based approach is certainly possible in Excel. You can use a combination of Excel formulas and sorting to achieve this. Here is a step-by-step guide:
Set Up Your Spreadsheet:
Assume your data is organized with columns like Player, Challenger, Opponent, and Outcome.
- Columns Setup:
- Column A: Player Names
- Column B: Challenger Names
- Column C: Opponent Names
- Column 😧 Outcome (Win or Loss)
Create a New Column for Match Results:
- Column E: Match Result:
- Use a formula in column E to determine the match result based on the outcome. For example:
=IF(D2="Win", B2, IF(D2="Loss", C2, ""))
- This formula will populate the name of the opponent in column E for wins and the challenger for losses.
Create a New Column for Match Positions:
- Column F: Match Position:
- Use a formula in column F to find the position of the match result in column E:
=MATCH(E2, A:A, 0)
- This formula finds the position of the opponent/challenger in the list of players.
Update the Ranking:
- Ranking Column (G):
- Use the RANK function to create a dynamic ranking based on the match positions:
=RANK(F2, F:F, 0)
- This formula assigns a rank to each player based on the match positions.
Sort the Data:
- Sort the Data:
- Sort the data based on the ranking column (Column G) in descending order.
- Click on the header of the column you want to sort (e.g., G) and choose "Sort Largest to Smallest."
Now, as matches are played and outcomes are entered, the ranking will automatically update based on the match results. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Hi NikolinoDE and many thanks for the quick and helpful reply.
The columns work to highlight the winner, and to locate current position in the ladder (league), which is a good step forward. The ranking though is based on the winner moving above the person that they have beaten, so this is still not working.
It doesn't matter how many points they have, just that the challenger 'leapfrogs' all other players in between their original position and the opponent they have won against. (For this reason, I don't think the sort function is not useful). If the opponent wins, no change in ranking happens.
eg
1. dave
2. phil
3. ian
If Ian challenges and beats dave, he moves ahead on the ladder.
1. ian
2. dave
3. phil
Many thanks in advance!
James
In this case, if the goal is to create a dynamic ladder where the challenger leapfrogs over the defeated opponent and all players in between, you can implement a more complex solution using Excel formulas.
Assuming your ladder starts in column A, and the challenger, opponent, and outcome information are in columns B, C, and D respectively, follow these steps:
Step 1: Identify the Matched Rows
- Column E: Matched Rows:
- Use the following formula in cell E2 and drag it down for all rows:
=IF(OR(B2="", C2="", D2=""), "", IF(D2="Win", MATCH(C2, $A$2:$A$100, 0), MATCH(B2, $A$2:$A$100, 0)))
- This formula will find the match positions for wins and losses. If no match is found, it leaves the cell blank.
Step 2: Determine the New Positions
- Column F: New Positions:
- Use the following formula in cell F2 and drag it down:
=IF(E2="", "", IF(D2="Win", ROW(B2) + E2 - ROW(C2), ROW(C2) + E2 - ROW(B2)))
- This formula calculates the new position for the winner (if the outcome is a win) based on the challenger and opponent positions.
Step 3: Implement the Dynamic Ranking
- Column G: Dynamic Ranking:
- Use the RANK function to create a dynamic ranking based on the new positions:
=IF(F2="", "", RANK(F2, F:F, 0))
- This formula assigns a rank to each player based on the new positions.
Step 4: Display the Ranked Ladder
- Display the Ranked Ladder:
- Use the INDEX and MATCH functions to display the ranked ladder. Assuming your original ladder is in column A, use the following formula in a new column (e.g., H2):
=IFERROR(INDEX($A$2:$A$100, MATCH(ROW(), $G$2:$G$100, 0)), "")
- Drag this formula down for all rows.
Now, as matches are played and outcomes are entered, the ranking will automatically update based on the challenger leapfrogging over the defeated opponent and all players in between.
This solution assumes that you have a maximum of 100 players in your ladder (adjust the range in formulas as needed). If you have a larger or smaller number of players, adjust the range accordingly.
- 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
