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.
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.
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
I think I have successfully crashed a working copy of the Excel workbook into you OneDrive folder.
(I also told Microsoft how lousy the Excel online experience is for development, given that I never reference data other than by defined name and usually employ named Lambda functions!)
I think the online experience could offer a workable sharing environment provided it is possible to sort out data protection at a granular level (otherwise the winner's laurels would always go to your most dishonest member!)
- Hi Peter, I have replied in the onedrive file, but C&P here as well.
This is based on the above file Tennis ladder – Peter V1: I've been able to update a score, but the errors still appear as before Peter (#NAME! Etc..). As you said before, you're on 365, and I'm on an older setup (2019 version 2312). If this is now about compatibility of versions, then MS wins... SO frustrating....Frustrating, yes. But at least we seem to have identified the problem. To access the file you will have to work with Excel 365 online and not your 2019 licensed version. The only redeeming feature is that your club members could also access the shared file to enter their challenges and record match results.
As an aside, I have always felt that purchasers of Excel 2019 were sold a pup. The calculation engine was already totally obsolete when it came out because the new dynamic array calculation did not make the cut when the intended functionality was frozen.
Things seem to have gone wrong in the 'simple' structured references in the MatchTable which is throwing #NAME! errors before one even gets to the ladder calculation step where the 'magic' happens.
The working formula that calculates the Result column on my copy is
= LET( completed, OR(MatchTable[@[Sets1]:[Sets2]] = 2), result, IF([@Sets1]>[@Sets2], "Win", "Loss"), IF(completed, result, "") )I could equally have used
= LET( completed, OR([@Sets1]=2, [@Sets2]=2), result, IF([@Sets1]>[@Sets2], "Win", "Loss"), IF(completed, result, "") )and, to be honest, I think that is clearer. The important thing with the table is that the formulas must propagate down the entire column and become its default formula.
As for sharing, I have relatively little experience of that. You have shared the OneDrive version of the file and that could offer a way forward. In corporate environments SharePoint is widely used. Perhaps once we have got your version of the spreadsheet up and going, you could post a supplementary question on the Forum relating to the collaborative use of spreadsheets and hope to get a wider response.
A further thought. If you update the OneDrive version of the file to the version that has gone wrong, I may be able to work on it and correct any issues. That would, however, be as much of an experiment for me as for you!
Thanks Peter - I've added the new code into the results column though without success. (screen grab attached).New code inc.
If you select the entire body of the table, what shows in the Name box to the left of the formula bar?
Could you select a cell in the result field and show a copy of the formula that is displayed. #NAME! errors should be easy to track down.