Peter Bartholomew, October 2022
Download World Cup 2022 Templates
We have yet to find what has changed in the world of football these past four years since the FIFA Russia World Cup in 2018. What became evident in preparing the prediction template for Qatar 2022, though, was that over the same period Excel has changed out of all recognition. The templates may look similar in terms of their layout but the formulas that drive the analysis of the Group stages are utterly different.
Instead of a series of hidden helper ranges with worksheet formulas progressively analysing the scores there is a single formula:
Instead of a series of hidden helper ranges with worksheet formulas progressively analysing the scores there is a single formula:
that extracts the scores from the Group results and generates the Group Standings Table sorted by points, goal difference and goals scores. So how was it done?
Group Standings Table showing a dynamic split range
Modern Excel
To get to this point, Excel has undergone a sequence of seismic changes that can change the face of the humble spreadsheet forever. Firstly, in September 2018 dynamic arrays were announced at the Microsoft Ignite event in Orlando Florida. The pain of writing traditional CSE Array formulas was replaced by a fluid dynamic experience in which arrays simply sized themselves effortlessly.
The next change was the LET function that allowed named variables to be defined and used within a formula, so allowing complex nested formulas to be laid out as a sequence of readable steps.
The third major change was LAMBDA which lets the developer reuse a formula by passing parameters to it. In the example above, the results from one of the Groups are passed as an argument to the function Analyseλ(). The final step was to introduce helper functions that allow data to be passed into a Lambda function row/column/cell at a time and the calculation results collected together afterwards to form a new output array, as is done for our standings table.
The next change was the LET function that allowed named variables to be defined and used within a formula, so allowing complex nested formulas to be laid out as a sequence of readable steps.
The third major change was LAMBDA which lets the developer reuse a formula by passing parameters to it. In the example above, the results from one of the Groups are passed as an argument to the function Analyseλ(). The final step was to introduce helper functions that allow data to be passed into a Lambda function row/column/cell at a time and the calculation results collected together afterwards to form a new output array, as is done for our standings table.
Description
The group stage user interface is designed to be intuitive for the purpose of entering the match results, but that does not mean that it is convenient for the purpose of analysis. Data is entered on alternate rows and the team associated with any given goal tally may be found to the left or the right of the score itself.
Results as collected on the user interface
Result array
As with the 2018 template, the decision was made to reduce the match results as input, to a 4x4 array in which each row represents the results achieved by a particular team and the ‘not available’ is merely as statement that no team plays itself. The results were extracted in this format by using a Lambda function ToArrayλ defined by:
The function ToArrayλ
The function accepts the Group results ‘s’ and reads to scores from alternate lines, for each goal tally, recording both the scoring team and the opponent using the INDEX function. The ‘for’ and ‘away’ goals are stacked to form an intermediate form for the table:
Stacked results
|
Sorted by team (for and against)
|
From there, the WRAPROWS function is used to return the 4x4 array. Note that unplayed matches are recorded as #N/A to distinguish them from goalless draws.
The Main Routine
Rather than continuing the calculations within this function, essentially treating it as a program module, control is returned to the main function, Analyseλ( ).
The main function Analyseλ()
As already discussed, the first step simply to calculate the 4x4 array of scores. Each row represents the scores achieved by the corresponding team. Equally the transpose gives the score of goals conceded by each team. This allows the entire series of results for games played, wins, draws and losses as well as goals scored, goals conceded, and points gained to be derived from simply pairwise comparisons. In each case BYROWS feeds the scores row by row into the Sumλ Lambda function
Which returns the row sums ready to be assembled into the array. The advantage of returning arrays rather than copy down results is that it allows the entire standings table to be assembled using HSTACK and sorted into rank order by sorting according to points, goal difference and goals scored in turn.
Once all the matches are played the Group Winner and Runner-up are looked up by team name from the group standings and the relevant country flags are displayed.
Once all the matches are played the Group Winner and Runner-up are looked up by team name from the group standings and the relevant country flags are displayed.
In Summary
The use of dynamic arrays and Lambda functions has allowed substantial calculations to be performed without resorting to helper arrays. Entire array operations such as filtering and sorting, which traditionally were largely manual processes, can be carried out with ease and, most important of all, once written, the Analyseλ( ) function can be applied to each of the other groups without modification.
Backstage User Interface
Our World Cup 2022 spreadsheet has a Backstage UI with clickable hyperlinks that navigate to articles in our website. Hover over images to view the URLs.
This complex custom user interface was designed using the Ribbon Commander (RC) framework. Download our macro-enabled workbook to find-out how easy it is to generate a dynamic ribbon UI with VBA alone and generate the XML behind this Office user interface (UI) using Ribbon Commander.
Please feel free to adapt this Ribbon Commander code to suit your Office Excel projects!
This complex custom user interface was designed using the Ribbon Commander (RC) framework. Download our macro-enabled workbook to find-out how easy it is to generate a dynamic ribbon UI with VBA alone and generate the XML behind this Office user interface (UI) using Ribbon Commander.
Please feel free to adapt this Ribbon Commander code to suit your Office Excel projects!
Custom Office Backstage UI
Download
Download World Cup 2022 Templates
|
Download the RC UI
workbook (xlsm) |
What Do You Think?
I would love to get your input on how to make this Excel World Cup template better. If you notice any bugs, have a new feature request or would like to suggest modifications to existing features, please let us know in the comments section below. We look forward to your feedback :)
