Create a ranking

[JWesty]

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 above that player. Which could be one, two or three places above depending on the pre match positions.
The outcome will be either win/loss -no draw.

I suspect that a points based solution will not work, but rather a solution that moves names above others in a ranking, based on outcome. 

Any help here would be super appreciated... 

How do I add an attachment here?

Thanks James

[#2]

@JWesty 

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.

1. 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:

1. 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:

1. 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:

1. 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:

1. 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.

[#3]

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

[#4]

@JWesty 

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

1. 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

1. 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

1. 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

1. 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.

[#5]

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=1284103...

[#6]

@JWesty 

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.

[#7]

Thank you for the extra detail - I will re-run in Excel. (Just using Google sheets to share with you for greater context)

[#8]

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].

[#9]

@JWesty 

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.

[#10]

@JWesty 

This is a 365 implementation that relies upon Lambda functions.

It uses REDUCE to update the ladder for each game result in turn.

"Worksheet formula"
= REDUCE(ladder, SEQUENCE(ROWS(GameTbl)), Updateλ)

"Updateλ(current, idx)"
= LET(
    challenger, INDEX(GameTbl, idx, 2),
    challenged, INDEX(GameTbl, idx, 3),
    outcome,    INDEX(GameTbl, idx, 4),
    aspired,    IF(current = challenged, challenger, NA()),
    removed,    IF(current <> challenger, current, NA()),
    reordered,  TOCOL(HSTACK(aspired, removed), 3),
    IF(outcome, reordered, current)
  )

I hope the attached demonstrates the calculation works despite looking nothing like a 'proper' spreadsheet!

[#11]

@NikolinoDE 

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.

[#12]

@NikolinoDE @PeterBartholomew1 

(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

[#13]

@PeterBartholomew1 

Many thanks Peter. The outcome is what I'm after.

The formulae from @NikolinoDE helped me to get the other columns set up, so this is good progress!

I'm not an advanced user, and so have to apologise now for the next question - how do I adjust it for my spreadsheet that I shared images of, and where do I place the code?

I can't even see on your spreadsheet where the formula is, or where it's pointing to. 

I've tried to understand it and it's a level above my knowledge or guestimatability 😞

p.s. You attached the spreadsheet - is it possible for me to do that? (I'm new to this forum).

[#14]

@JWesty 

If it is any consolation, there are very few Excel users that would not find the formula fiercely difficult to understand!  The formula is in cell K4 and returns an array that spills into the range K4:K11.  The formula itself uses two defined names.  The first 'ladder' is simply a named reference to the range C4:C11 which initialises the process.  The second name 'Updateλ' is a named Lambda function and is where the going gets difficult for most.  The content can be read and edited using Name Manager but is easier to see if the Microsoft Advanced Formula editor is downloaded as an add-in.

The ability to attach a spreadsheet is a privilege earnt by continued participation within the forum.  It will come in due course.  You could probably attach your workbook to a private message to one of us if you want it posted. 

Meanwhile, if you copy the sheet containing my formula to your workbook it should set the function up in your workbook (as well as the odd inter-workbook reference that would need to be deleted)

If you are not already familiar with them, you will need to read up on:

1. Dynamic array formulas

2. Defined Names

3. The LET function which uses variables to hold values for reuse

4.The LAMBDA function which accepts values from function arguments

5. Array shaping functions, TOCOL in particular.

The good news about Lambda functions, though, is that they can be used without fully understanding how they work; the same thing as goes for every built-in Excel function.

[#15]

@JWesty 

Most of the things I have described this far are standard features of Excel 365.  Something I tried specific to your problem, was to form two arrays

and then use TOCOL to reduce the stacked array to a single column, ignoring the #N/A errors, to give the updated ladder.

[#16 · Accepted solution]

@JWesty 

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.

[#17]

You're literally a mind reader! I've sat for an hour not knowing where to start, but wanting to not seem like a total thicky!

Let me read your note; and thank you.

[#18]

@PeterBartholomew1 

Hi Peter,

You've nailed it in terms of getting the ranking to work. 

Now I need to understand enough to be able to transpose that onto my sheet. That's the tricky bit ;

You'll have guessed I'm not a coder (!), so being use to standard excel lookups as defined by cell or column references, this seems like a different world. 

Some questions......

How do I see the instruction that says what the initial ranking table is, and how do I adjust that to be a column on a different sheet in the same workbook?

When I change the results box, and add more results, the current ranking in Cell O returns to "Name", and no names; and nothing I do returns the original list. 

I've created a onedrive link (there is nothing else in my One drive, so hoping no back door alerts are going to go off on my PC) Documents.

Thanks, James

[#19]

@JWesty 

How are you getting on?  I have added some of the match analysis contained in your workbook.  I normally perform calculation using dynamic arrays rather than tables but there is no reason to pull you any further out of your comfort zone!  I haven't used a direct cell reference or non-array formulas  for 7 years now (it used to be CSE and was horrible) so my methods are not exactly mainstream (yet?). 

Besides listing the competitor match analysis presented in initial rank order, I have provided an alternative version using the latest rank order.

[#20]

@PeterBartholomew1 

Hi Peter,

Many thanks for the updated sheet - 2 steps forwards for sure and it's got everything included that's needed.  I'm being beaten by tech wizardry though, as I mentioned in my last post:  If I enter any additional match result rows, or in this case not adding a row, but just including results in row 17 between Martin and Charlie, or change a result, the calculations in columns G & H return blank on the Matches tab, and the Current Ranking table contents on LMTL show an error #Name! (Please see attached).

Final question - to share this in real-time (I would normally work in Google Sheets for sharing ease), what would your suggestion be (assuming attaching to an email wouldn't work for the way people are interacting (whattapp and Google)  - Most Excel functions that I come across are transposable to Google, but uploading this to google raised errors. You might not be familiar with Google sheets, but I thought it would be worth asking in case you knew.

Many thanks,

James