This video shows how to use the new LAMBDA function in Excel to loop through repeated calculations - both a 'fixed-size' loop (like a FOR loop in VBA) and an open-ended loop (like a WHILE loop in VBA).
The example problems I used are from the @msexcelesports Europe battle - you can watch the four original participants trying it out here (as ever, note that what I can do after preparing is not comparable to what they have to do seeing it for the first time!):
https://www.youtube.com/watch?v=1t8yj-NXph8
You can buy the case materials for $10 from the FMWC here (note that the e-sports problems include the questions and answers, but not a worked solution like the cases from the regular rounds):
https://www.fmworldcup.com/product/continent-battles-sequence/
To be clear, I don't get commission or anything like that for referring you, and you don't need to buy the case to watch the video.
The xkcd comic that made a brief appearance is this one:
https://xkcd.com/710/
Sections:
00:00 Introduction
04:00 Fixed-size loop (Fibonacci)
08:12 Open-ended loop (Collatz)
12:52 Last example
16:44 Closing thoughts
These are the LAMBDAs that I wrote in the video (with some substitutions, because apparently you can't put greater than or less than signs in a YT video description!).
Collatz
_____________________
=LAMBDA(start, [n],
LET(
newN, IF(ISOMITTED(n), 1, n),
IF(
start = 1,
newN,
Collatz(IF(ISODD(start), 3 * start + 1, start / 2), newN + 1)
)
)
)
Fib1K
_____________________
=LAMBDA(t_1, t_2, N,
IF(
N [less than sign] 3,
CHOOSE(N, t_1, t_2),
Fib1K(t_2, MOD(t_1 + t_2, 1000), N - 1)
)
)
SeqStep
_____________________
=LAMBDA(arr,
LET(
cols, COLUMNS(arr),
seq, SEQUENCE(, cols),
SWITCH(
seq,
1,
INDEX(arr, 2),
cols,
INDEX(arr, cols - 1),
ROUND((INDEX(arr, seq - 1) + INDEX(arr, seq + 1)) / 2, 0)
)
)
)
Level5
_____________________
=LAMBDA(arr, n, IF(n = 0, INDEX(arr, 7), Level5(SeqStep(arr), n - 1)))