New Contributor

# Lambda ARRAY.SHAKE

My native language is German, so I use a Creating-Sub for the Lambda (with selfexplaining content):

``````Sub InsertAndUseOfALambdaFunction()
"=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``````

# Betreff: Lambda ARRAY.SHAKE

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()
"=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``````