Recursive LAMBDA for progressive SMALL calculations

[macfuller]

I have inherited a golf pool spreadsheet (see below) for the Masters. You initially pick 10 golfers for the tournament on the assumption that some (even Jordan Spieth!) will miss the cut. The 6 best golfers of your picks after the cut have the difference in their score from the tournament leader added up for all 4 rounds. If you don't have 6 golfers who made the cut you're out of the pool. deChambeau didn't get much love from our group this year!

You can see in cell I3 that currently Player 1's golfers are 24 above deChambeau, with Koepka and Morikawa having not finished Round 1. Invalid or missing rounds leave the Actual Total column at 0.

The SUMPRODUCT function in I3 works fine.

=LET(
     ScoreDifference, SUMPRODUCT(
        NOT(ISBLANK(tblResults[Player 1])) * (tblResults[[Actual Total]:[Actual Total]]<>0),
        tblResults[[Actual Total]:[Actual Total]] - Leader_Score),
    Result, IF(ScoreDifference = 0, 999, ScoreDifference),
    Result
    )

But in order to handle the cut from 10 to 6 players it is necessary to eliminate the "x" against the dud players, although one can just stop reporting the scores on the others. But since I just copy the round numbers from the ESPN scorecard into the spreadsheet I either blank out every non-pool golfer or delete the "x".
I'd like a simpler way to recursively calculate the SMALL function using the # Selections value in F3. I'm new to LAMBDA functions but I'm hoping there's a way to actually do something in the pseudo-code below...

=LET(
    ScoreArray, -get the Actual Total array for the Player-
    SmallTotal, Recursive LAMBDA(RunningTotal, SMALL(ScoreArray,6...1),
    RunningTotal
)

Any chance of this? Thanks.

2024 MCC Masters.xlsx
	A	B	C	D	E	F	G	H	I
2						# Selections			Player 1
3						6		Cumulative Strokes Above Leader	24
4								Rank	5
5	Golfers not being tracked should have nothing or zero entered for each round
6	Cut or withdrawn golfers should get "CUT" or "WD" in the Status column
7	or have all round scores removed						Leader Score
8		Scoreboard					65
9	Golfer	Day 1	Day 2	Status	Day 3	Day 4	Actual Total	Num Picks	Player 1
10	Åberg, Ludvig						0	1
12	Bhatia, Akshay						0	1
13	Bradley, Keegan						0	1
15	Cantlay, Patrick	71					71	1
16	Clark, Wyndham	73					73	1
21	Day, Jason						0	2
25	Eckroat, Austin	74					74	1
28	Fitzpatrick, Matt	71					71	1
35	Hadwin, Adam	75					75	1
37	Harman, Brian						0	1
38	Hatton, Tyrrell						0	1
44	Hovland, Viktor	71					71	4	x
54	Koepka, Brooks						0	8	x
60	Matsuyama, Hideki	76					76	7	x
61	McCarthy, Denny	74					74	1
62	McIlroy, Rory	71					71	5	x
66	Morikawa, Collin						0	1	x
68	Niemann, Joaquín	70					70	3
71	Pavon, Matthieu						0	1
73	Rahm, Jon	73					73	9
76	Schauffele, Xander	72					72	7
77	Scheffler, Scottie	66					66	11	x
84	Spieth, Jordan						0	3
Pool Selections

Cell Formulas
Range	Formula
I2	I2	=INDEX(tblResults[#Headers],COLUMN())
I3	I3	=LET( ScoreDifference, SUMPRODUCT( --(NOT(ISBLANK(INDIRECT("tblResults[" & I2 & "]")))) * (tblResults[Actual Total]<>0), tblResults[Actual Total] - Leader_Score), Result, IF(ScoreDifference = 0, 999, ScoreDifference), Result )
I4	I4	=RANK(I3,$I$3:$AF$3,1) +COUNTIF($I3:I3,I3)-1
G8	G8	=IFERROR( SMALL( tblResults[Actual Total], COUNTIF( tblResults[Actual Total], "<=" & 0 ) + 1 ), "n/a" )
G10,G12:G13,G15:G16,G21,G25,G28,G35,G37:G38,G44,G54,G60:G62,G66,G68,G71,G73,G76:G77,G84	G10	=LET( GolferScore, [@[Day 1]] + [@[Day 2]] + [@[Day 3]] + [@[Day 4]], Round1Played, COUNTA([Day 1]) > 0, Round2Played, COUNTA([Day 2]) > 0, Round3Played, COUNTA([Day 3]) > 0, Round4Played, COUNTA([Day 4]) > 0, GolferCut, NOT(ISBLANK([@Status])), c_01, "If a round has been played but there's no score for the golfer, set total score to 0", IgnoreRound1, AND([@[Day 1]]=0,Round1Played), IgnoreRound2, AND([@[Day 2]]=0,Round2Played), IgnoreRound3, AND([@[Day 3]]=0,Round3Played), IgnoreRound4, AND([@[Day 4]]=0,Round4Played), KeepScore, (NOT(GolferCut) * NOT(IgnoreRound1) * NOT(IgnoreRound2) * NOT(IgnoreRound3) * NOT(IgnoreRound4)) = 1, IF(KeepScore, GolferScore, 0) )
H10,H12:H13,H15:H16,H21,H25,H28,H35,H37:H38,H44,H54,H60:H62,H66,H68,H71,H73,H76:H77,H84	H10	=COUNTA(tblResults[@[Player 1]:[P20]])

Named Ranges
Name	Refers To	Cells
Leader_Score	='Pool Selections'!$G$8	I3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I9:AB9	Expression	=COUNTA(I$10:I$98) <> $F$3	text	NO

 

[macfuller]

BTW, My LAMBDA attempt is getting a #VALUE error, probably because I don't understand how to properly record the variables.

My LAMBDA function is defined as FindSmallLambda in Name Manager as

= LAMBDA(SmallValue,Data,IF(SmallValue>0,FindSmallLambda(SmallValue-1,Data,SMALL(Data,SmallValue)),0))

And the cell calling it has

=LET(
    ScoreArray,  NOT(ISBLANK(tblResults[Player 1])) * ( tblResults[[Golfer Total]:[Golfer Total]] - Leader_Score) * (tblResults[[Golfer Total]:[Golfer Total]]<>0),
    RunTotal, FindSmallLambda($F$3, ScoreArray),
    RunTotal
)