LAMBDA for golf handicap?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9
Bob3842003941373841


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...
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,280
Office Version
  1. 365
Platform
  1. Windows
This seems to work.

Book1
ABCDEFGHIJKL
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9HDCP
2Bob384200384137384139.2
3Steve4240364138036374038.2
4Mike42404241381536374038.4
Sheet1
Cell Formulas
RangeFormula
L2:L4L2=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

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKL
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9H'Cap
2384200394137384139.4
33842450394137384140.2
436363636000364036
5404439404140.8
6424443
7420000004443
Sheet3
Cell Formulas
RangeFormula
L2:L7L2=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))
 
Solution

tboulden

Board Regular
Joined
Jan 14, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKL
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9LAMBDA
2384200394137384139.4
33842450394137384140.2
436363636000364036
5404439404140.8
6424443
7420000004443
8
Scratch
Cell Formulas
RangeFormula
K2K2=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:K7K3=Handicap(A3:I3)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
.. a more compact LET formula:

macfuller_1.xlsm
BCDEFGHIJKL
1Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Handicap
2384200394137384139.4
33842450394137384140.2
436363636000364036
5404439404140.8
6424443
7420000004443
Sheet4
Cell Formulas
RangeFormula
L2:L7L2=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

Active Member
Joined
Apr 30, 2014
Messages
299
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 14, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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)."
 

Forum statistics

Threads
1,143,620
Messages
5,719,784
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top