Calculate league standings in a fixture list

p45cal · #1 01-04-2024, 06:24 PM

try in AI2:

Code:

=LET(rws,($D$2:$D$97=$D2)*($G$2:$G$97=$G2),TRANSPOSE(INDEX(SORT(CHOOSE({1,2},VALUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,FILTER($Z$2:$AA$97,rws))&"</b></a>","//b")),VALUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,FILTER($N$2:$O$97,rws))&"</b></a>","//b"))),,-1),,2)))

Revert to FILTERXML formula for columns AB:AC (TEXTSPLIT is a problem at your end). No don't, see edit below.

Bed.

edit. use

Code:

=MATCH(ROUND(Z2,9),ROUND(SORT(VALUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,FILTER($Z$2:$AA$97,($D$2:$D$97=$D2)*($G$2:$G$97=$G2)))&"</b></a>","//b")),,-1),9),0)

in cell AB2 and copy down and across, it's safer (includes VALUE function).

Matin · #2 01-04-2024, 06:48 PM

Weldone mate works like a charm.

Without these formulas I may had to do these the old fashion way which was to copy paste results week by week (something I did during the summer). This would save me hours/days/weeks.
Are you available on the weekend/next week because I'm planning to mass calculate my data then? Your formulas look sound and solid and hopefully I won't be needing you again but I'll let you know if I encounter any issue.

I'm really gratefule for your help again.

Matin · #3 01-06-2024, 05:57 PM

Hi Pascal,

The formula works great when teams have played equal number of games every matchday/week. However, when there are less than 10 matches played for whatever reason the positions are calculated wrongly as you can seen in the spreadsheet below for week 24. 'par' v 'udi' is postponed in that particular week and thus teams are ranked from 1 to 18. The expected results are in columns N and O. What do you think is a solution for that little problem?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cross-reference with full context a numbered list inside another multilevel list (list style)	MatLcq	Word	0	02-01-2021 06:00 AM
Calculate a Date	shawn.low@cox.net	Mail Merge	5	12-12-2019 03:22 PM
are there any good free golf league programs out there	kener40	Other Software	0	03-28-2014 05:54 PM
calculate age	userman	Excel	8	06-02-2012 10:59 PM
Calculate formula base of list menu	rkeles	Excel	4	09-22-2010 12:38 AM