Forum Discussion
Lambda ARRAY.SHAKE
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", RefersToR...
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