Lambda ARRAY.SHAKE

%3CLINGO-SUB%20id%3D%22lingo-sub-2197315%22%20slang%3D%22de-DE%22%3ELambda%20ARRAY.%20Shake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2197315%22%20slang%3D%22de-DE%22%3E%3CP%3EMy%20native%20language%20is%20German%2C%20so%20I%20use%20a%20Creating-Sub%20for%20the%20Lambda%20(with%20selfexplaining%20content)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20InsertAndUseOfALambdaFunction()%0AActiveWorkbook.Names.Add%20Name%3A%3D%22ARRAY.SHAKE%22%2C%20RefersToR1C1%3A%3D%20_%0A%22%3DLAMBDA(a%2CLET(%20x%2CROWS(a)%2C%20y%2CCOLUMNS(a)%2C%20z%2CRANDARRAY(x*y)%2C%20v%2CMATCH(z%2CSORT(z))%2C%20u%2CINDEX(v%2CSEQUENCE(x%2C%2C0)*y%2BSEQUENCE(%2Cy))%2C%20INDEX(a%2CTRUNC((u%2By-1)%2Fy)%2CMOD(u-1%2Cy)%2B1)))%22%0AActiveWorkbook.Names(%22ARRAY.SHAKE%22).Comment%20%3D%20%22%22%0ARange(%22A1%22).Formula2R1C1%20%3D%20%22%3DARRAY.SHAKE(%7B%22%22will%22%22%2C%22%22Covid%22%22%2C%22%22ever%22%22%2C%22%22end%22%22%7D)%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2197315%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

 

 

 

1 Reply

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