LAMBDA for golf handicap?

[macfuller]

Looking at the LAMBDA function it seems like it will do some marvelous things. I'm struggling to get my head around how I could have a single golf handicap function for our league to replace a ton of helper columns.

Your league rules will vary of course, but our league will take the average of the last 7 non-zero rounds minus the lowest and highest scores. Players miss weeks during the season so they can have zero scores.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9
Bob	38	42	0	0	39	41	37	38	41

Bob's average is thus 39.4, taking the 276 stroke total and subtracting the high of 42 and the low of 37.

For less than 6 rounds we just take the average of all non-zero, and for 6 rounds we'll subtract the highest and not the lowest to get our five rounds.

So I would expect the LAMBDA to be recursive, counting backward until it hits either 7 non-zero scores or the golfer name AND figuring the min and max along the way (excluding zero scores of course), then subtracting the appropriate number of scores and taking the average of what's left.

What has me boggled is trying to track the 3 variables during the recursive search...

 

[lrobbo314]

This seems to work.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9		HDCP
2	Bob	38	42	0	0	38	41	37	38	41		39.2
3	Steve	42	40	36	41	38	0	36	37	40		38.2
4	Mike	42	40	42	41	38	15	36	37	40		38.4
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=LET(tbl,FILTER(B2:J2,B2:J2>0,""), col,COLUMNS(tbl), flt,INDEX(tbl,1,SEQUENCE(1,SWITCH(col,5,5,6,6,7),SWITCH(col,5,1,6,1,7,1,col-7+1))), s,SORT(flt,,,1), fs,SWITCH(col,5,s,6,INDEX(s,1,SEQUENCE(,5)),INDEX(s,1,SEQUENCE(1,5,2))), AVERAGE(fs))

 

[macfuller]

Thank you!
It's as convoluted as I feared, and I see you went with LET instead of LAMBDA. I will spend a while figuring this out, but I appreciate your brilliance.

 

[Peter_SSs]

You might also consider this 'LET' option. It is a bit shorter and also still gives an average if there are less than 5 above zero scores rather than an error.

macfuller.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9		H'Cap
2	38	42	0	0	39	41	37	38	41		39.4
3	38	42	45	0	39	41	37	38	41		40.2
4	36	36	36	36	0	0	0	36	40		36
5	40	44	39	40	41						40.8
6	42	44									43
7	42	0	0	0	0	0	0	44			43
Sheet3

Cell Formulas
Range	Formula
L2:L7	L2	=LET(rng,INDEX(B2:J2,IFERROR(AGGREGATE(14,6,SEQUENCE(,COLUMNS(B2:J2))/(B2:J2>0),7),1)):J2,fltr,FILTER(rng,rng>0),n,MIN(7,COUNT(fltr)),(SUM(fltr)-MAX(fltr)*(n>5)-MIN(fltr)*(n=7))/MIN(5,n))

 

[tboulden]

Taking advantage of the sample data set up already in Peter_SSs submission, and I match his solutions, so that makes me feel alright. Making a LAMBDA from a LET is very easy, just give parameter names to your LET variables that get inputs from the sheet. Not the cleverest formula, but comprehensibility is important, and the most obscure thing here is TRIMMEAN. I'll think about a recursive solution, but definitely overkill.

LAMBDA_Testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9		LAMBDA
2	38	42	0	0	39	41	37	38	41		39.4
3	38	42	45	0	39	41	37	38	41		40.2
4	36	36	36	36	0	0	0	36	40		36
5	40	44	39	40	41						40.8
6	42	44									43
7	42	0	0	0	0	0	0	44			43
8
Scratch

Cell Formulas
Range	Formula
K2	K2	=LAMBDA(myRng, LET( rng,myRng, nonZero,FILTER(rng,rng>0,0), k,COLUMNS(nonZero), latest,FILTER(nonZero,SEQUENCE(1,k)>(k-7),0), ct,COLUMNS(latest), IF( ct = 7,TRIMMEAN(latest,2/7), IF( ct = 6,AVERAGE(SMALL(latest,5)), AVERAGE(latest) ) ) ) )(A2:I2)
K3:K7	K3	=Handicap(A3:I3)

 

[Peter_SSs]

.. a more compact LET formula:

macfuller_1.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9		Handicap
2	38	42	0	0	39	41	37	38	41		39.4
3	38	42	45	0	39	41	37	38	41		40.2
4	36	36	36	36	0	0	0	36	40		36
5	40	44	39	40	41						40.8
6	42	44									43
7	42	0	0	0	0	0	0	44			43
Sheet4

Cell Formulas
Range	Formula
L2:L7	L2	=LET(k,COUNTIF(B2:J2,">0"),n,MIN(k,7),nums,INDEX(FILTER(B2:J2,B2:J2>0),SEQUENCE(n,,k,-1)),(SUM(nums)-MAX(nums)*(n>5)-MIN(nums)*(n=7))/MIN(5,n))

 

[macfuller]

Thanks everyone. I'm not sure if there are a lot of golfers out there or it's just a good puzzle.

The solutions provided still are using various array functions - taking more than one cell at a time (FILTER, SEQUENCE, ...). My admittedly elementary reading on LAMBDA indicates that its strength (and speed when repeated many times) is its recursiveness. To me that sounds like it's more of a VBA approach? Taking one cell at a time and testing, then moving on to the next.

So what I thought that would mean for a LAMBDA is something like

Start
max = 0
min = 100
n = 0
total = 0

Is the current cell non-numeric or n = 7?
No
If the cell > 0 then
n = n + 1
total = total + cell
max = max(cell, max)
min = min(cell, min)
Endif
Skip to next cell left and do again recursive

Yes
If n <= 5 then result = total / n
If n = 6 then result = (total - max) / 5
If n = 7 then result = (total - max - min) / 5

Done

Or I'm totally missing the point of LAMBDA.

 

[tboulden]

indicates that its strength (and speed when repeated many times) is its recursiveness

I don't think we have a good way of determining the resource usage of LAMBDAs, but I would assume that most built-in functions have been optimized to a great degree. I think LAMBDAs shine where you can't get built-in functions to do what you need in a normal, formula-based manner. Being able to recurse is very useful, but there are some limitations with respect to depth of recursion, and see comment from here:

"Furthermore, do note that the current operand stack limit in Excel is 1,024. This should be borne in mind together with calculation times, as the current recursion limit is set as 1,024 divided by (number of lambda parameters + 1)."