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.
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!
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!
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.
Adding players below an Excel Table causes it to extend and most formulas will require more space to spill that does not conflict with other data or the Table itself.
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) https://1drv.ms/f/s!Avf59RbR7DWbjhOzT6WIc0CI2xRF?e=WJwgjf
Thanks, James
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.
- 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.
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.
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.
