Mar 09 2021 12:29 PM
My native language is German, so I use a Creating-Sub for the Lambda (with selfexplaining content):
Sub InsertAndUseOfALambdaFunction()
ActiveWorkbook.Names.Add Name:="ARRAY.SHAKE", RefersToR1C1:= _
"=LAMBDA(a,LET( x,ROWS(a), y,COLUMNS(a), z,RANDARRAY(x*y), v,MATCH(z,SORT(z)), u,INDEX(v,SEQUENCE(x,,0)*y+SEQUENCE(,y)), INDEX(a,TRUNC((u+y-1)/y),MOD(u-1,y)+1)))"
ActiveWorkbook.Names("ARRAY.SHAKE").Comment = ""
Range("A1").Formula2R1C1 = "=ARRAY.SHAKE({""will"",""Covid"",""ever"",""end""})"
End Sub
Mar 14 2022 01:43 AM - edited Mar 14 2022 02:01 AM
The following shows a severe bug caused by name managed LAMBDA. It only occurs there. If you define a LAMBDA name in a cell (see cell D1), the problem does not occur. - The bug is stable with all statistical functions as SUM, MIN, MAX, STDEV and so on. - Here, it shows wrong [B1]: -23 , where ist should correctly be [C1]: 19, using type conversion --, where there should be none necessary [A8].
The function DECUM simply decumulates {2;5;7;19} values of a row or column to {2;3;2;12}.
Sub LAMBDAaNameManagerBug()
ActiveWorkbook.Names.Add Name:="DECUM", RefersToR1C1:= _
"=LAMBDA(x,LET(d,SEQUENCE(ROWS(x),COLUMNS(x)),INDEX(x,d)-(d>1)*INDEX(x,d-1)))"
[A1].Formula2R1C1 = "=DECUM({2;5;7;19})"
[A6] = "Name Mgr"
[B1].FormulaR1C1 = "=SUM(DECUM({2;5;7;19}))"
[B6] = "WRONG!"
[C1].Formula2R1C1 = "=SUM(--DECUM({2;5;7;19}))"
[C6] = "ok!"
[D1].Formula2R1C1 = _
"=LET(Decum,LAMBDA(x,LET(d,SEQUENCE(ROWS(x),COLUMNS(x)),INDEX(x,d)-(d>1)*INDEX(x,d-1))),Decum({2;5;7;19}))"
[D6] = "no Name Mgr"
[E1].Formula2R1C1 = _
"=LET(Decum,LAMBDA(x,LET(d,SEQUENCE(ROWS(x),COLUMNS(x)),INDEX(x,d)-(d>1)*INDEX(x,d-1))),SUM(Decum({2;5;7;19})))"
[E6] = "ok!"
[A8].Formula2R1C1 = "=ISNUMBER(R[-7]C#)"
Rows("6:6").HorizontalAlignment = xlRight
End Sub