FIFA World Cup 2022. A demonstration of Lambda functions.

[Peter Bartholomew]

2022 FIFA World Cup Qatar Free Predictor Template For Excel (spreadsheet1.com)

Again, this year I worked with Petros to produce an update for the 2022 FIFA World Cup.  The striking thing was that so much has changed within Excel since 2018.

The formulae to reduce the Group results table to Group standings was entirely reworked to remove helper arrays and instead use 24 lines of Excel formulas resulting in the final form

 Analyseλ(Matches) 

which was then applied to each of the other groups without modification.

The description is found by following the link

LAMBDA Functions Explained

Please click here to learn how our World Cup 2022 Qatar template is made.

Your comments are invited on the Spreadsheet1 site but perhaps this is the place for informed comment on Lambda (rather than soccer)!

[#2]

Thank you for sharing. This is a fascinating article! I'm going to through this at my leisure and post my thoughts later.

[#3]

Unreliable! Nice Excel works.

[#4]

I am receiving mixed messages there (grin). Possibly a problem in translation?

[#5]

@Peter Bartholomew 

After reviewing your solution extensively I've come to a few conclusions.

1. the BYROW portion of Analyseλ seems inevitable given the arrangement of the data and the approach to cleaning up the original matrix with ToArray.  I played with using multiple arrays with MAP to get comparable results, but stopped myself because while it may be possible to achieve the results in 'fewer' steps, the solution would be a lot messier and difficult to read.

2. the inability of COUNTIF/SUMIF (and others) to handle dynamic arrays really stands out.  Your solution to separate the goals from the teams and compare the two matrices with BYROW and the help of Sumλ is as clean as any approach I can think of here.

3.  I did consider the old blunt instrument approach to the range using a series of SUMIFS and COUNTIFS but ultimately, I'd need to consolidate and stack.  A longer, methodical approach is still cleaner and easier for others to understand.

I did play with MUNIT a bit and see where I could use it in the Word Search Lambda to support diagonal words.  The only thing holding me back there is the fact that diagonal "arrays" really aren't a thing in Excel and any solution would be to see if it could be done.

[#6]

@Patrick2788 

I don't think MUNIT is ever likely to achieve "Most Valuable Function" status!

I suppose one could always define

DiagSplitλ
= LAMBDA(w,
       LET(
            n,      LEN(w),
            k,      SEQUENCE(n),
            letter, MID(w,k,1),
            diag,   MUNIT(n),
            IF(diag,letter,"")
       )
   )

= DiagSplitλ(word)

[#7]

Diagonals may be more trouble than it's worth, now that I look at MUNIT again. It seems I'd probably go the familiar route of text manipulating: joining, splitting, wrapping, etc.