Forum Discussion

lupo1's avatar
lupo1
Copper Contributor
Mar 09, 2021

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", 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

 

 

 

excel

1 Reply

  • lupo1's avatar
    lupo1
    Copper Contributor
    Mar 14, 2022

    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