# ARF  	DIY Array Recursive Function kit



## Xlambda (Jul 15, 2021)

ARF *!! recursive !!* *DIY* *A*rray *R*ecursive *F*unction kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
*ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))*
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
*ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))*
AGG study.xlsxABCDEFGHIJKLMNOPQRS1Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)2Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible3To append 2D arrays we already have APPENDNHV4Writing the recursive function following the syntax draft, function name, let's define APP:5APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))6The appending "engine" functionality is extremely simple IF(s=j,x,ai)7Is equivalent with this :8=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))9a10ba1=APP((C11:D11,C14:E14,C17:D17),,)112a2a2#N/A12b3413a212#N/A14b341516a317121819General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array20a121a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))22xw223atwx23txa2425so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))26Now let's define our specific recursive function (ASU) using the kit syntax27ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))28a229a2-1=ASU((B21:D23,B29:D33,B36:C37),,)30qac23atwx31dc2-123acdq32-13-12q332dd3435a3Other function on minisheet36q2AFLAT372q3839This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.40It will be nice to see others function creations posted here!!!41ARF postCell FormulasRangeFormulaA8,G29,G21,G10A8=FORMULATEXT(A9)A9:A11A9=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))G11:I13G11=APP((C11:D11,C14:E14,C17:D17),,)G22:L22G22=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))G30:M32G30=ASU((B21:D23,B29:D33,B36:C37),,)Dynamic array formulas.


----------



## Xlambda (Jul 17, 2021)

More examples will follow .... increasing in complexity...but with help of ARF kit template....everything is simple and straight forward
AGG study.xlsxABCDEFGHIJKLMNOPQRS1Task 1: Extract the top k largest unique values of each array of an array "range"  (no duplicates)23samplestep 1: study and create our custom made F(x,k) for a single array using LARGE41LARGE does not exclude duplicatesTo exclude duplicates we need UNIQUE52=LARGE(A4:A10,SEQUENCE(3))=LARGE(UNIQUE(A4:A10),SEQUENCE(3))635573448443941052D sample=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))11a4512344since we need horizontal arrays, SEQUENCE(,3) will do the trick1333=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))14b5543154#N/Aour F(x,k)=LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k))16(mixed data,blanks,numeric,text,errors)17(AFLAT filters everything)1819step 2: replace F(x,k) in the ARF kit template with LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)) and define the new function20(also always check that all the arguments trough the syntax are consistent, no variable names duplicates..etc)21ARF function kit: ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))22Now we can define our specific recursive function for the task: ALARGE23 ALARGE(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ALARGE(a,k,IF(s=j,LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)),ai),j-1))))2425a1=ALARGE((B26:D29,B31:E33,B35:E39),2,,)26536k,276Imp Note:2742g54A recursive function will work fine also for single arrays28d5698=ALARGE(B35:E39,5,,)293k79876530a2=ALARGE((B26:D29,B31:E33,B35:E39),3,,)31w235k,376532432-15433302zx98734a3357b5y=ALARGE((B26:D29,B31:E33,B35:E39),4,,)36w659k,476543797585432387d489876392qwk40ARF post 2Cell FormulasRangeFormulaC5,G5,H35,H30,N28,H25,I13,G10C5=FORMULATEXT(C6)C6:C8C6=LARGE(A4:A10,SEQUENCE(3))G6:G8G6=LARGE(UNIQUE(A4:A10),SEQUENCE(3))G11:G13G11=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))I14:K14I14=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))D15D15=NA()H26:I28H26=ALARGE((B26:D29,B31:E33,B35:E39),2,,)N29:R29N29=ALARGE(B35:E39,5,,)H31:J33H31=ALARGE((B26:D29,B31:E33,B35:E39),3,,)H36:K38H36=ALARGE((B26:D29,B31:E33,B35:E39),4,,)Dynamic array formulas.


----------



## Xlambda (Jul 18, 2021)

AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZ1Task 2: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 1-13) (no k argument)2will use ARF function kit template:3ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))4Let's define ARF=AGG for F(x)=F(fn,o,x)=AGGREGATE(fn,o,x) 5paying attention to the argument distribution consistency trough the function will get to:6AGG(a,fn,o,ai,i)=LAMBDA(a,fn,o,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGG(a,fn,o,IF(s=j,AGGREGATE(fn,o,x),ai),j-1))))7a1a2a3a4AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )8536w2357b5yt-3yh-2function_num arg.options argument942g432-1w6-1-1h4l54↓function↓values to be ignored10d5602zx-22.5-585h1-331Average0Ignore nested Subtotal & Aggregate functions113k73d4-331-25k2Count1Ignore hidden rows and nested Subtotal & Aggregate functions122qwk3Counta2Ignore error values and nested Subtotal & Aggregate functions134Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions14o=65Min4Ignore nothing15fn123456789101112136Product5Ignore hidden rows164.6912724536001.71.6412.82.5557Stdev.S6Ignore error values172.29125-101.91.7203.43.1228Stdev.P7Ignore hidden rows and error values18213208-5-30240004.13.92617152.5-19Sum191.613205-36480003.23219.99.23510Var.S20=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)11Var.P2112Median22single cell formula for all fn13Mode.Sngl23=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)14Largefn 14-19 with k argument, next post, creating AGGK recursive function244.6912724536001.71.6412.82.55515Small252.29125-101.91.7203.43.12216Percentile.Inc26213208-5-30240004.13.92617152.5-117Quartile.Inc271.613205-36480003.23219.99.23518Percentile.Exc2819Quartile.Exc29if we want only min,max and sum, in a single cell formula fn={5,4,9}30=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)31274132-152033-582634-352135AGG postCell FormulasRangeFormulaD16:P19D16=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)D20D20=FORMULATEXT(D16)D23,D30D23=FORMULATEXT(D24)D24:P27D24=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)D31:F34D31=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)Dynamic array formulas.


----------



## Xlambda (Jul 18, 2021)

AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1Task 3: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 14-19) (with k argument)2will use ARF function kit template:3ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))4Let's define ARF=AGGK for F(x)=F(fn,o,x,k)=AGGREGATE(fn,o,x,k) 5paying attention to the argument distribution consistency trough the function will get to:6AGGK(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGGK(a,fn,o,k,IF(s=j,AGGREGATE(fn,o,x,k),ai),j-1))))7a1a2a3a4AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )8536w2357b5yt-3yh-2function_num arg.options argument942g432-1w6-1-1h4l54↓function↓values to be ignored10d5602zx-22.5-585h1-331Average0Ignore nested Subtotal & Aggregate functions113k73d4-331-25k2Count1Ignore hidden rows and nested Subtotal & Aggregate functions122qwk3Counta2Ignore error values and nested Subtotal & Aggregate functions13o=64Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions14k={1,3}k={1,2}k={0,0.2,0.7,1}k={0,1,2,3,4}5Min4Ignore nothing15fn=14fn=15fn=16fn=176Product5Ignore hidden rows167623235.67235677Stdev.S6Ignore error values1753-10-11.235-122358Stdev.P7Ignore hidden rows and error values1886-5-3-5-1.64.48-5-12.5589Sum1955-3-3-3-245-3-234510Var.S2011Var.P21k={0.1,0.2,0.7,0.9}k={1,2,3}12MedianThe functions that require argument k22fn=18fn=1913Mode.Snglfunctionmeaning of k23236735614Large14LargeReturn the k'th largest value24-1035123.515Small15SmallReturn the k'th smallest value25-4.2-2.24.87.6-1.52.55.516Percentile.Inc16Percentile.IncReturn the k'th percentile0<=k<=126-3-2.245-234.517Quartile.Inc17Quartile.IncReturn the k'th quartile0<=k<=4(<5)2718Percentile.Exc18Percentile.ExcReturn the k'th percentile0<k<128 all FORMULATEXT syntax=AGGK((a1,a2,a3,a4),fn,o,k,,)19Quartile.Exc19Quartile.ExcReturn the k'th quartile1<=k<=3(<4)29AGGK postCell FormulasRangeFormulaA16:B19A16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),14,6,{1,3},,)D16:E19D16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),15,6,{1,2},,)G16:J19G16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),16,6,{0,0.2,0.7,1},,)L16:P19L16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),17,6,{0,1,2,3,4},,)G23:J26G23=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),18,6,{0.1,0.2,0.7,0.9},,)L23:N26L23=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),19,6,{1,2,3},,)Dynamic array formulas.


----------



## Xlambda (Jul 18, 2021)

AGG study.xlsxABCDEFGHIJKLMNOPQRSTU1a1Task 4 : a. Extract 2nd smallest and 2nd largest of each array of an array "range"2536o=6,fn={14,15},k=2342g=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)4d566353k740b. Extract Percentile.Inc and Percentile.Exc values for 10 and 90% , for each array of an array range6a27-3o=6,fn={16,18},k=0.17w2355-3=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)8432-12.82902zx-0.2-1o=6,fn={16,18},k=0.910a3-2.8-4.2=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)117b5y-2.8-36.2712w6-1-14.2513-22.5-586.87.6143d4-355152qwk16a4c. Extract 1st and 3rd Quartile.Inc and Quartile.Exc values for each array of an array "range"17t-3yh-2o=6,fn={17,19},k=118h4l54=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)195h1-33332031-25k21o=6,fn={17,19},k=321-1-1.5=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)22-2-2662333.52455.52544.526AGGK post 2Cell FormulasRangeFormulaG3,M21,J18,M10,J7G3=FORMULATEXT(G4)G4:H7G4=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)J8:K11J8=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)M11:N14M11=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)J19:K22J19=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)M22:N25M22=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)Dynamic array formulas.


----------



## Xlambda (Jul 19, 2021)

AGG study.xlsxABCDEFGHIJKLMNOPQRST1a1Task 5: All task 4 ops in a single cell2536342go=6,fn={14,15,16,18,16,18,17,19,17,19},k={2,2,0.1,0.1,0.9,0.9,1,1,3,3}4d5653k7=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)6a2632.826.2733667w23540-0.2-14.252133.58432-17-3-2.8-4.26.87.6-1-1.555.5902zx5-3-2.8-355-2-244.510a3117b5y12w6-1-113-22.5-58143d4-3152qwk16a417t-3yh-218h4l54195h1-332031-25k21AGGK post 3Cell FormulasRangeFormulaG5G5=FORMULATEXT(G6)G6:P9G6=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)Dynamic array formulas.


----------



## Xlambda (Jul 19, 2021)

AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1a1Task 6: Function_num argument 13 of AGGREGATE function is for MODE.SNGL2536Let's create a recursive function that is able to calculate MODE.MULT for each array of an array "range"342gNote: MODE.MULT is not among the functions covered by AGGREGATE because it can deliver an array result, but our function kit design can handle this with ease, so we will cover it.4d56step 1:=MODE.MULT(A2:C5)=TRANSPOSE(MODE.MULT(A2:C5))53k7Identify a formula F(x) for a single array55366a237w23568432-190-1zxsince ARF formula kit template is designed to handle 1D horiz arrays, our F(x) will be:10a3F(x)=TRANSPOSE(MODE.MULT(x))117b5y12w6-1-1step 2:13-22.5-58define new recursive function AMM, using the kit template 143d4-3ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))156qwk16a4AMM(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))17t-3yh-2=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)18h4l54536195h1-3323-12031-25k6-1#N/A2155#N/A2223To handle the NA error , since the result vector carrier is ai, we modify in formula ai with IFERROR(ai,"") (after IF(j=0,ai,AMM(….  )24AMM(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))25=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)265362723-1Note: To visualize the 2 behaviors at once , with errors and no errors, we needed 2 dif functions, that’s why we have AMMnoER286-1295530AMMCell FormulasRangeFormulaL4,O4,G25,G17L4=FORMULATEXT(L5)L5:L7L5=MODE.MULT(A2:C5)O5:Q5O5=TRANSPOSE(MODE.MULT(A2:C5))G18:I21G18=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)G26:I29G26=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)Dynamic array formulas.


----------



## Xlambda (Jul 19, 2021)

*Task 7*: Wrap up all AGGREGATE related functions (AGG,AGGK,AMM) functionality in "all in one" tool function *T_AGG(a,fn,o,k,ai,i)* *!! recursive !!*
So far*, *is the first explicit function here based on ARF kit template apart of all intermediate "study" ones. The possibilities are limitless . Choose AGGREGATE functionality because really is a function that does a lot, and attach to it recursive array "range" versatility could be an useful tool. Hope that nobody will hate me for so many posts, it's only a fine journey that I think, deserves to be shared.

```
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),
          T_AGG(a,fn,o,k,IF(s=j,
             IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),
               ai),j-1))
    )
)
```
AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1a1Task 7: Wrap up all AGGREGATE related functions (AGG,AGGK, AMM) functionality in one function (T_AGG)2536step 1 : Identify F(x,fn,o,k) , needs to have all arguments possible in AGGREGATE, and according to fn argument 1<=fn<=13 or 14<=fn<=19 proper AGGREGATE forms are called (with k or without k)342gAlso we added MODE.MULT functionality for a new fn value fn=20. All this in a simple IFS formula.4d56Note: if fn<>(14,19), even if k has a value , will be ignored because the formula calls the AGGREGATE without k form. 53k7F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")6a2step 2: we create the tool recursive function using the kit template, as above (T_AGG)7w235o=6k,ignored8432-1fn123456789101112132090-1zx4.556912724536001.6671.571412.7782.4695553610a31.8899125-102.1472.025174.6114.0992223-1117b5y2.26913208-5-90720004.2264.0629.517.8616.49366-112w6-1-11.61513205-36480003.153.027219.9239.16355513-22.5-58=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)↑143d4-3=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),W8,6,,,)156qwkk220.220.22(fn 20 is in the upper section because does not need k argument)16a4fn14151617181917t-3yh-263353518h4l544-1-0.42-12195h1-337-3-1.63-2.232031-25k5-3-23-2.2321=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)2223single cells formulas24=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)254.556912724536001.6671.571412.7782.46955261.8899125-102.1472.025174.6114.09922272.26913208-5-90720004.2264.0629.517.8616.4936281.61513205-36480003.153.027219.9239.16352930=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)31633535324-1-0.42-12337-3-1.63-2.23345-3-23-2.2335T_AGGCell FormulasRangeFormulaW9:Y12,I9:U12I9=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)I13,H21I13=FORMULATEXT(I9)U14U14=FORMULATEXT(W9)H17:M20H17=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)I24,H30I24=FORMULATEXT(I25)I25:U28I25=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)H31:M34H31=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)Dynamic array formulas.


----------



## Xlambda (Jul 20, 2021)

*Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array*, create a *!!recursive!!* tool *T_FUS(a,f,u,s,ai,i)*, based on ARF kit template. Calls AFLAT
a: single array or array "range" (a1,a2...an) *( All recursive functions created with the kit work fine for a single array or an array "range")*
f: filter argument, 0, filters out only blanks/null strings,-1,filters text, 1, filters numeric values
u: unique argument, 0, does nothing, 1, extracts unique values
s: sort argument, 0, no sorting, 1, ascending order, -1,descending order
ai,i: arguments carriers of the recursive functionality, always ignored
*Note: The order of operations triggered by the arguments is from left to right, or as an inside out representation SORT(UNIQUE(FILTER(..)))*

```
=LAMBDA(a,f,u,s,ai,i,
    LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j,
           LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),
              ai),j-1))
    )
)
```
AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array, create a recursive tool T_FUS(a,f,u,s,ai,i) 23step 1: identifying arguments and their values4fus5-10-1a1a2a3a46010536w2357b5yt-3yh-271142g432-1w6-1-1h4l48textno uniquedescendingd560-1zx-22.585h1-339no blanksuniqueno sort3k73d-331-25k10numericascending6qwk1112step 2: Identify F(x,f,u,s) for a single array13 =LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))14check functionality for a3 array15=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)16-3-2-12.53567817F(x,f,u,s) will be whatever comes after LET, we do this to save defining another sepparate lambda, the variables will be predifined beforehand by the main function in the kit structure18F(x,f,u,s)=LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values"))1920step 3: create the tool lambda using ARF recursive function kit template21ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))22As I said before, we have to check consistency of variables and arguments when using the kit23We notice that the kit has "s" variable already , and the F(x) has an "s" argument (sort , so we have to change that, we change the varable "s" in the kit with "q"24Also we replace ai, final vector that carries the result with IFERROR(ai,""), to handle eventual NA errors25ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ARF(a,IF(q=j,F(x),ai),j-1))))26Now replacing F(x) will get to T_FUS, our function for the task:27T_FUS(a,f,u,s,ai,i)=LAMBDA(a,f,u,s,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j,
LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),ai),j-1))))28Note: LET inside LET is very versatile29f,0,u,0,s,0 (no blanks,no unique,no filter) (remove blanks,rest unchanged)30=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)3153642gd563k732w235432-10-1zx337b5yw6-1-1-22.583d-36qwk34t-3yh-2h4l45h1-3331-25k3536f,1,u,1,s,1 (unique numbers ascending order)f,-1,u,1,s,-1 (unique text descending order)37=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)38234567kgd39-102345zxw40-3-2-12.535678ywqkdb41-3-21345ytlkh4243If filter has no results example44a1a2=T_FUS((B45:C46,E45:F46),1,1,1,,)=T_FUS((B45:C46,E45:F46),-1,1,1,,)45ab12 abc46ca311234748If you want other value than null string spot "v" variable inside formula , after second LET …v,FILTER(y,z,"")…and change "" , for example with "not found"4950If wrong argument value5152=T_FUS((B45:C46,E45:F46),1,2,1,,)53check values54check values55T_FUSCell FormulasRangeFormulaA15,A52,P44,I44,L37,B37,B30A15=FORMULATEXT(A16)A16:I16A16=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)B31:T34B31=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)B38:J41B38=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)L38:Q41L38=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)I45:K46I45=T_FUS((B45:C46,E45:F46),1,1,1,,)P45:R46P45=T_FUS((B45:C46,E45:F46),-1,1,1,,)A53:A54A53=T_FUS((B45:C46,E45:F46),1,2,1,,)Dynamic array formulas.


----------



## Xlambda (Jul 27, 2021)

A1RF  *!! recursive !!* *DIY* *A*rray *1* *R*ecursive *F*unction kit (Single Array Recursive Function kit)
*A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE( n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))*
a: array
d: "direction" argument, 0 or ignored, horizontal (by rows), 1, vertical (by columns)
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
*Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns*
AGG study.xlsxABCDEFGHIJKLMNOPQRSTUV1Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns2Since an array can have 2 dimensions will define 2 kits A1RFH and A1RFV (array one recursive function kit horizontal/vertical) or a single function A1RF for both "directions"(extra arg. "d")39.1A1RFH4We will still use universal ARF function kit template structure:5ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))6we replace:7nr.arrays n,AREAS(a) with nr.rows n,ROWS(a)8x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle9will replace it with x,INDEX(a,j,) extracts one full row at a time for every iteration operation in the recursive cycle10A1RFH(a,ai,i)=LAMBDA(a,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,A1RFH(a,IF(s=j,F(x),ai),j-1))))11129.2A1RFV13we replace:14nr.arrays n,AREAS(a) with nr.columns n,COLUMNS(a)15x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle16will replace it with x,INDEX(a,,j) extracts one full column at a time for every iteration operation in the recursive cycle17also we have to change the orientation of the building result engine18will replace s,SEQUENCE(n) with s,SEQUENCE(,n)19A1RFV(a,ai,i)=LAMBDA(a,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,A1RFV(a,IF(s=j,F(x),ai),j-1))))20Note: As a general note, to change the direction or orientation of an array outcome we can also use double transpose.21A1RFV(a)=TRANSPOSE(A1RFH(TRANSPOSE(a))22239.3A1RF24We noticed so far that second part of the functions (the building result engine, the ai argument) always stays the same, regardless the orientation 25IF(s=j,F(x),ai). This means, we can introduce a separate argument for orientation/direction , d, with 2 values to cover both scenarios26d: 0 or ignored for horizontal (by rows), 1, for vertical (by columns)27A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))2829Let's put this to test for a simple operation, SUM, function name A1SUM using the kit template so, F(x)=SUM(x)30A1SUM(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1SUM(a,d,IF(s=j,SUM(x),ai),j-1))))3132sampled=0check33=SEQUENCE(6,4)=A1SUM(B34#,,,)=SUM(B34:E34)3412341010355678262636910111242423713141516585838171819207474392122232490904041d=142=A1SUM(B34#,1,,)436672788444check45=SUM(B34:B39)466672788447A1RF postCell FormulasRangeFormulaB33,B45,B42,G33,J33B33=FORMULATEXT(B34)B34:E39B34=SEQUENCE(6,4)G34:G39G34=A1SUM(B34#,,,)J34:J39J34=SUM(B34:E34)B43:E43B43=A1SUM(B34#,1,,)B46:E46B46=SUM(B34:B39)Dynamic array formulas.


----------



## Xlambda (Jul 15, 2021)

ARF *!! recursive !!* *DIY* *A*rray *R*ecursive *F*unction kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
*ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))*
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
*ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))*
AGG study.xlsxABCDEFGHIJKLMNOPQRS1Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)2Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible3To append 2D arrays we already have APPENDNHV4Writing the recursive function following the syntax draft, function name, let's define APP:5APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))6The appending "engine" functionality is extremely simple IF(s=j,x,ai)7Is equivalent with this :8=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))9a10ba1=APP((C11:D11,C14:E14,C17:D17),,)112a2a2#N/A12b3413a212#N/A14b341516a317121819General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array20a121a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))22xw223atwx23txa2425so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))26Now let's define our specific recursive function (ASU) using the kit syntax27ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))28a229a2-1=ASU((B21:D23,B29:D33,B36:C37),,)30qac23atwx31dc2-123acdq32-13-12q332dd3435a3Other function on minisheet36q2AFLAT372q3839This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.40It will be nice to see others function creations posted here!!!41ARF postCell FormulasRangeFormulaA8,G29,G21,G10A8=FORMULATEXT(A9)A9:A11A9=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))G11:I13G11=APP((C11:D11,C14:E14,C17:D17),,)G22:L22G22=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))G30:M32G30=ASU((B21:D23,B29:D33,B36:C37),,)Dynamic array formulas.


----------



## Xlambda (Jul 28, 2021)

*Task 10:* To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows) *!! recursive !! T_A1GGH(a,fn,o,k,ai,i)*

```
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,
      T_A1GGH(a,fn,o,k,IF(s=j,
         IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),ai),j-1))
    )
)
```
AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ1Task 10: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows)2Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)3F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")4Using A1RFH kit.5A1RFH(a,ai,i)=LAMBDA(a,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,A1RFH(a,IF(s=j,F(x),ai),j-1))))6Will get to:7T_A1GGH(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,T_A1GGH(a,fn,o,k,IF(s=j,
IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),ai),j-1))))89o,6,k,ignoredAGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )10samplefn1234567891011121320function_num arg. "fn"options argument "o"11122a4473.3367714482.161.97204.673.893224↓function↓values to be ignored128bb814c141147148125443.463441291188141Average0Ignore nested Subtotal & Aggregate functions13d3535d446532251.151161.33143352Count1Ignore hidden rows and nested Subtotal & Aggregate functions1466g8g87468623041.151281.33176683Counta2Ignore error values and nested Subtotal & Aggregate functions1599595576695911252.192424.8479954Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions16d3a3443.546431440.580.5140.330.253.53345Min4Ignore nothing17=T_A1GGH($A$11:$G$16,J10,6,,,)=T_A1GGH($A$11:$G$16,X10,6,,,)6Product5Ignore hidden rows187Stdev.S6Ignore error values19o,68Stdev.P7Ignore hidden rows and error values20k220.220.229Sum21fn14151617181910Var.S2242231.4311Var.P2314881181112MedianThe functions that require argument k2453343413Mode.Snglfunctionmeaning of k2586676714Large14LargeReturn the k'th largest value2695575715Small15SmallReturn the k'th smallest value274333.533.516Percentile.Inc16Percentile.IncReturn the k'th percentile0<=k<=128=T_A1GGH($A$11:$G$16,J21,6,J20,,)17Quartile.Inc17Quartile.IncReturn the k'th quartile0<=k<=4(<5)2918Percentile.Exc18Percentile.ExcReturn the k'th percentile0<k<130single cell formula fn,{1,2,…,13},o,6,k,ignored19Quartile.Exc19Quartile.ExcReturn the k'th quartile1<=k<=3(<4)31=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)custom created20Mode.Mult323.3367714482.161.97204.673.8932331147148125443.4634412911834446532251.151161.33143357468623041.151281.331763676695911252.192424.8479373.546431440.580.5140.330.253.533839single cell formula fn,{14,15,16,17,18,19},o,6,k,{2,2,0.2,2,0.2,2}single cell formula for multiple k arg.40=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)=T_A1GGH(A11:G16,14,6,{1,2,3},,)4142231.437444214881181114148435334345534486676788645955757999464333.533.544347A1RF post 2Cell FormulasRangeFormulaJ10:V10J10=SEQUENCE(,13)X11:Y16,J11:V16J11=T_A1GGH($A$11:$G$16,J10,6,,,)J17,J28J17=FORMULATEXT(J11)U17U17=FORMULATEXT(X11)J22:O27J22=T_A1GGH($A$11:$G$16,J21,6,J20,,)J31,V40,J40J31=FORMULATEXT(J32)J32:V37J32=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)J41:O46J41=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)V41:X46V41=T_A1GGH(A11:G16,14,6,{1,2,3},,)Dynamic array formulas.


----------



## Xlambda (Jul 28, 2021)

*Task 11:* To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns) *!! recursive !! T_A1GGV(a,fn,o,k,ai,i)*

```
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,
      T_A1GGV(a,fn,o,k,IF(s=j,
        IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values"),
          ai),j-1))
    )
)
```
AGG study.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Task 11: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns)2Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)3F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")4Taking in consideration the orientation we also have to modify TRANSPOSE(MODE.MULT(x)) to MODE.MULT(x)5F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values")6Using A1RFV kit:7A1RFV(a,ai,i)=LAMBDA(a,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,A1RFV(a,IF(s=j,F(x),ai),j-1))))8Will get to:9 T_A1GGV(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,T_A1GGV(a,fn,o,k,IF(s=j,
IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,MODE.MULT(x),TRUE,"check values"),ai),j-1))))1011sampleo,6,k,ignoredo,61218d69dfn=T_A1GGV($A$12:$F$18,H13,6,,,)fnk=T_A1GGV($A$12:$F$18,P13,6,Q13,,)132b69313.333114773.51424145894142b3ga264446415228365315a85853776666160.228365316414393471458941723114773.5174c5g545183653180.21.48365318714d8546448125442252304911251441923114773.51972.163.4641.1551.1552.1910.5772081.97231120.521920441628421422104.667121.3331.3334.80.33323113.88991140.2524123114773.5251328369326=T_A1GGV($A$12:$F$18,H27,6,,,)27202836932841458542930single cell formula fn,{1;2;…;13},o,6,k,ignored 31(fn multiple argument array should follow rows constantant array syntax (";" separator instead of ",")32=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)333.333114773.5single cell formula for multiple k arguments34644464=T_A1GGV(A12:F18,14,6,{1;2;3},,)3577666671458943671458944145894371836534836933844812544225230491125144392.163.4641.1551.1552.1910.577401.97231120.541204416284214424.667121.3331.3334.80.333433.88991140.25443114773.54528369346A1RF post 3Cell FormulasRangeFormulaI12,P34,I32,I26,R12I12=FORMULATEXT(I13)R13:W18R13=T_A1GGV($A$12:$F$18,P13,6,Q13,,)I27:N28,I13:N25I13=T_A1GGV($A$12:$F$18,H13,6,,,)I33:N45I33=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)P35:U37P35=T_A1GGV(A12:F18,14,6,{1;2;3},,)Dynamic array formulas.


----------



## smozgur (Jul 28, 2021)

@Xlambda / @schardt679 

Did you get the new functions in the latest update?








						Excel First Look At Lambda Helper ByRow And By Col 2415
					

A first look at the new Excel functions BYROL and BYCOL. These functions, released to Insiders Fast this week are designed to be used with LAMBDA functions.  Read more about the 7 functions here...




					www.mrexcel.com
				




They are very good improvements. Although I didn't have a chance to play with them in detail, I can say that I already love MAP, BYs, and REDUCE.


----------



## Xlambda (Jul 28, 2021)

smozgur said:


> @Xlambda / @schardt679
> 
> Did you get the new functions in the latest update?
> 
> ...


Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. ?✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind ? Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!


----------



## smozgur (Jul 28, 2021)

Xlambda said:


> Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. ?✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
> Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind ? Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!


 

These are really good improvements. I can't wait to play with them.


----------



## schardt679 (Jul 28, 2021)

I did get them. I'll have to play around with them. There's not a lot of info on them yet.  This is huge. I think it will help make currently long LAMBDAs a lot shorter.  It will be cool to mix BYCOL, BYROW, SCAN, MAP, REDUCE, MAKEARRAY, and ISOMITTED to rewrite existing LAMBDAs and incorporate them into new ones.


----------



## Xlambda (Jul 28, 2021)

Very first glimpse of new function BYROW
AGG study.xlsxABCDEFGHIJKLMNO1Very first glimpse on new!! BYROW function2like in MrExcel video 3=BYROW(A4:C5,LAMBDA(a,MAX(a)))41323542556works fine7Seems that any function that returns a single result, applied to every row of an array , will produce an array of results accordingly8=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))9210411second largest value of each row, also works because returns a single value1213=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))14#CALC!15top 2 largest values of each rows does not work, because it does not return a single result16k argument of AGGREGATE {1,2} is forcing an array result, not a single result17{1;2} does not work either, obviously1819=T_A1GGH(A4:C5,14,6,{1,2},,)2032215422my function, (Task 11 of this post) can handle this, means that I still have a job ?2324=BYROW(A4:C5,LAMBDA(a,SORT(a)))25#CALC!26SORT also does not work , array result27I will make one that does ?✌28BYCOL,BYROWCell FormulasRangeFormulaE3,E24,E19,E13,E8E3=FORMULATEXT(E4)E4:E5E4=BYROW(A4:C5,LAMBDA(a,MAX(a)))E9:E10E9=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))E14E14=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))E20:F21E20=T_A1GGH(A4:C5,14,6,{1,2},,)E25E25=BYROW(A4:C5,LAMBDA(a,SORT(a)))Dynamic array formulas.


----------



## schardt679 (Jul 28, 2021)

Also as part of this update, it seems when working with dates and one is returned, Excel automatically updates the cell using the source date formatting

It seems it only does it for the first date in an array.

Using the MAKEARRAY function should help with the multiple answer for each row/column problem


----------



## Xlambda (Jul 28, 2021)

I wish but I don't think so, since specific arguments rows and columns are required
Book2ABCDEFG1figure it out a simple example of what MAKEARRAY does2=MAKEARRAY(2,3,LAMBDA(a,b,a+b))323443455equivalent of:6=SEQUENCE(2)+SEQUENCE(,3)723483459Sheet1Cell FormulasRangeFormulaB2,B6B2=FORMULATEXT(B3)B3:D4B3=MAKEARRAY(2,3,LAMBDA(a,b,a+b))B7:D8B7=SEQUENCE(2)+SEQUENCE(,3)Dynamic array formulas.


----------



## schardt679 (Jul 28, 2021)

It seems you would have to get the row and column count in the function itself. Then pass those into MAKEARRAY


----------



## Xlambda (Jul 15, 2021)

ARF *!! recursive !!* *DIY* *A*rray *R*ecursive *F*unction kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
*ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))*
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
*ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))*
AGG study.xlsxABCDEFGHIJKLMNOPQRS1Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)2Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible3To append 2D arrays we already have APPENDNHV4Writing the recursive function following the syntax draft, function name, let's define APP:5APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))6The appending "engine" functionality is extremely simple IF(s=j,x,ai)7Is equivalent with this :8=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))9a10ba1=APP((C11:D11,C14:E14,C17:D17),,)112a2a2#N/A12b3413a212#N/A14b341516a317121819General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array20a121a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))22xw223atwx23txa2425so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))26Now let's define our specific recursive function (ASU) using the kit syntax27ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))28a229a2-1=ASU((B21:D23,B29:D33,B36:C37),,)30qac23atwx31dc2-123acdq32-13-12q332dd3435a3Other function on minisheet36q2AFLAT372q3839This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.40It will be nice to see others function creations posted here!!!41ARF postCell FormulasRangeFormulaA8,G29,G21,G10A8=FORMULATEXT(A9)A9:A11A9=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))G11:I13G11=APP((C11:D11,C14:E14,C17:D17),,)G22:L22G22=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))G30:M32G30=ASU((B21:D23,B29:D33,B36:C37),,)Dynamic array formulas.


----------



## smozgur (Jul 29, 2021)

FYI: Another video from MrExcel about the new functions:








						PRODUCTIF With MAP REDUCE or SCAN  - 2416
					

Three more of the new Lambda Helper functions in Excel: MAP, REDUCE, and SCAN. In today's episode, see three different ways to create a PRODUCTIF function in Excel using MAP, REDUCE, or SCAN. Important points: MAP can take one or more arrays. MAP is looking at each individual cell, not just the...




					www.mrexcel.com


----------



## Xlambda (Aug 2, 2021)

smozgur said:


> FYI: Another video from MrExcel about the new functions:
> 
> 
> 
> ...


Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
Function arguments field works fine, took the brackets. ✌


----------



## smozgur (Aug 2, 2021)

Xlambda said:


> Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
> Function arguments field works fine, took the brackets. ✌


Thanks for the heads up. I just made the necessary changes to allow brackets and edited the AGGR function post.


----------



## Xlambda (Jul 14, 2022)

Check this out, another cool excel challenge from today's Jon-Excel Campus YT: Excel Formula Challenge: Even or Odd License Plates
*I was missing the "elegance" of recursive functions.*
*EO(c)* *E*ven/*O*dd function. *recursive !!!
c: *single value, single cell reference

```
=LAMBDA(c,
    LET(
        n, LEN(c),
        x, --RIGHT(c, 1),
        IF(n = 0, "Odd", IF(ISERR(x), EO(LEFT(c, n - 1)), IF(ISODD(x), "Odd", "Even")))
    )
)
```
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNO1License Plate ChallengeRule: If last digit of license plate is odd nr. than "Odd", else "Even", if no digits found => "Odd"21st solution2nd solution, entire clm vector, all steps3=REDUCE(A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))4=EO([@[Plate Number]])↓↓↓=--RIGHT(D6#,1)=IF(ISODD(+F6#),"Odd","Even")5Plate NumberOdd/Even↓↓↓↓↓↓↓↓↓=IFERROR(H6#,"Odd")6123M57Odd123577OddOdd71COU886Even18866EvenEvencheck results, both solutions81D48821Odd1488211OddOdd=AND(J6#=tblChallenge[Odd/Even])91DVT189Odd11899OddOddTRUE1021W7454Even2174544EvenEven1127U17Odd27177OddOdd122IGR878Even28788EvenEven132RDZ434Even24344EvenEven142SWQ185Odd21855OddOdd153FII697Odd36977OddOdd163QOW866Even38666EvenEven173RQC229Odd32299OddOdd183RSZ114Even31144EvenEven194FXL296Even42966EvenEven204KUC981Odd49811OddOdd214QKB212Even42122EvenEven224QRG241Odd42411OddOdd234RVF139Odd41399OddOdd245ACY568Even55688EvenEven255IYF234Even52344EvenEven265KFC128Even51288EvenEven275NFD419Odd54199OddOdd285RXA984Even59844EvenEven295TGB871Odd58711OddOdd305UEZ777Odd57777OddOdd315WKT763Odd57633OddOdd326LJT722Even67222EvenEven336MVB117Odd61177OddOdd346XZR265Odd62655OddOdd3571F27UJOdd71277OddOdd36745Y4IEven74544EvenEven3776736QEven767366EvenEven387FOB933Odd79333OddOdd397JCM371Odd73711OddOdd40884PCXEven8844EvenEven418JHC777Odd87777OddOdd428RDQ178Even81788EvenEven438TMW513Odd85133OddOdd449PMH329Odd93299OddOdd459UGW414Even94144EvenEven469UUL349Odd93499OddOdd47BEACHOdd#VALUE!#VALUE!Odd48G97C8TEven9788EvenEven49H1R226IEven12266EvenEven50KL7469COdd74699OddOdd51RUSSOdd#VALUE!#VALUE!Odd52S94COEven944EvenEven53T5A5MENOdd555OddOdd54YELOSTNOdd#VALUE!#VALUE!Odd55YOSEMITOdd#VALUE!#VALUE!Odd56ChallengeCell FormulasRangeFormulaD3D3=FORMULATEXT(D6)B4,F4,H4B4=FORMULATEXT(B6)J5,L8J5=FORMULATEXT(J6)D6:D55D6=REDUCE(A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))F6:F55F6=--RIGHT(D6#,1)H6:H55H6=IF(ISODD(+F6#),"Odd","Even")J6:J55J6=IFERROR(H6#,"Odd")L9L9=AND(J6#=tblChallenge[Odd/Even])B6:B55B6=EO([@[Plate Number]])Dynamic array formulas.


----------



## Xlambda (Jul 14, 2022)

*Array format, single cell formulas, 2 solutions, using recursive EO(c) and REDUCE*
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNOPQ11 solution, using EO(c)2=MAP(B5:B54,LAMBDA(x,EO(x)))3↓↓↓2 solution REDUCE, lambda formula4array↓↓↓=IFERROR(IF(ISODD(--RIGHT(REDUCE(B5:B54,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,""))),1)),"Odd","Even"),"Odd")5123M57OddOdd61COU886EvenEvencheck results, both solutions71D48821OddOdd=AND(F5#=D5#)81DVT189OddOddTRUE921W7454EvenEven1027U17OddOdd112IGR878EvenEven122RDZ434EvenEven132SWQ185OddOdd143FII697OddOdd153QOW866EvenEven163RQC229OddOdd173RSZ114EvenEven184FXL296EvenEven194KUC981OddOdd204QKB212EvenEven214QRG241OddOdd224RVF139OddOdd235ACY568EvenEven245IYF234EvenEven255KFC128EvenEven265NFD419OddOdd275RXA984EvenEven285TGB871OddOdd295UEZ777OddOdd305WKT763OddOdd316LJT722EvenEven326MVB117OddOdd336XZR265OddOdd3471F27UJOddOdd35745Y4IEvenEven3676736QEvenEven377FOB933OddOdd387JCM371OddOdd39884PCXEvenEven408JHC777OddOdd418RDQ178EvenEven428TMW513OddOdd439PMH329OddOdd449UGW414EvenEven459UUL349OddOdd46BEACHOddOdd47G97C8TEvenEven48H1R226IEvenEven49KL7469COddOdd50RUSSOddOdd51S94COEvenEven52T5A5MENOddOdd53YELOSTNOddOdd54YOSEMITOddOdd55Challenge 2Cell FormulasRangeFormulaD2D2=FORMULATEXT(D5)F4,H7F4=FORMULATEXT(F5)D5:D54D5=MAP(B5:B54,LAMBDA(x,EO(x)))F5:F54F5=IFERROR(IF(ISODD(--RIGHT(REDUCE(B5:B54,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,""))),1)),"Odd","Even"),"Odd")H8H8=AND(F5#=D5#)Dynamic array formulas.


----------



## Xlambda (Jul 14, 2022)

For fun, let's create our own *License Plate Generator.*
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Concept. Creating 30 random license plates , letters and numbers, 7 chars long.2Formula trick to give digits close probability as letters have, to be picked up by random engine.(2 sequences of digits 0 to 9) (20 digits+ 26 letters=> sequence dimension=46)3↓↓↓4=LET(a,SEQUENCE(46,,65),IF(a>90,RIGHT(a,1),CHAR(a)))=BYROW(L6#,LAMBDA(x,TEXTJOIN(,,x)))5↓↓↓=RANDARRAY(30,7,1,46,1)=INDEX(B6#,D6#)↓↓↓=MAP(T6#,LAMBDA(x,EO(x)))6A2361217141935WFLQNS9WFLQNS9Odd7B438688388D2FHH2HD2FHH2HEven8C7441917181443G8SQRN7G8SQRN7Odd9D274292212771D3VLGG1D3VLGGOdd10E121437247110LN1XGAJLN1XGAJOdd11F40231234046244B5W40X4B5W40XEven12G36423117373322065Q17V065Q17VOdd13H274017515383314QEO2714QEO27Odd14I1325363312622MY07LFVMY07LFVOdd15J21420253792UDTY1IBUDTY1IBOdd16K33303316442630747P8Z4747P8Z4Even17L331714344647QNCD0D7QNCD0DEven18M1721223523236QUV9B60QUV9B60Even19N431016392945277JP33917JP3391Odd20O721141941325GUNS56EGUNS56EEven21P163610367428P0J0GD2P0J0GD2Even22Q6340122443FC4LBD7FC4LBD7Odd23R2540293538723Y4392GWY4392GWEven24S31172122126165QUVAZP5QUVAZPOdd25T45845244435159H9X89O9H9X89OOdd26U10401828382421J4R22XUJ4R22XUEven27V312432381446335X62N075X62N07Odd28W441937453027128S1941L8S1941LOdd29X932101318359I6JMR9II6JMR9IOdd30Y143091931337N4ISCM1N4ISCM1Odd31Z1337249391641M1XI3P5M1XI3P5Odd321169138321920PIMH6STPIMH6STEven33222421929453619V6S390SV6S390SEven3434593932204619I36T0A9I36T0AEven354182436122422RBD0LXVRBD0LXVEven365Lic Plt Gen 1Cell FormulasRangeFormulaB4,T4B4=FORMULATEXT(B6)D5,V5,L5D5=FORMULATEXT(D6)B6:B51B6=LET(a,SEQUENCE(46,,65),IF(a>90,RIGHT(a,1),CHAR(a)))D6:J35D6=RANDARRAY(30,7,1,46,1)L6:R35L6=INDEX(B6#,D6#)T6:T35T6=BYROW(L6#,LAMBDA(x,TEXTJOIN(,,x)))V6:V35V6=MAP(T6#,LAMBDA(x,EO(x)))Dynamic array formulas.


----------



## Xlambda (Jul 14, 2022)

Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLM1Single cell formula. Creating n random license plates , letters and numbers, 7 chars long.23n 4045=LET(n,C3,s,SEQUENCE(46,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,46,1)),LAMBDA(x,TEXTJOIN(,,x))))6↓↓↓7↓↓↓=MAP(B8#,LAMBDA(x,EO(x)))8IX6KG32check nEven9RRBVZB3=ROWS(B8#)Odd10D2N300Z40Even11G82NVPIEven12NDT0OC7Odd13TZ746G5Odd14O69H5SROdd15H4N3O01Odd16Q8DV0X0Even17EPT4Y9QOdd18V2DCW97Odd19075BN3SOdd20VL91WO5Odd21R88Q8S8Even22J09DA3YOdd235W0WC6ZEven24BYJZA99Odd251152JW7Odd26RN02CJHEven277HZJ63FOdd285YIES07Odd29GKLX394Even30K8BTN0CEven31OP36E8DEven32NSZZ0OUEven3315R24C8Even34BT04CDGEven351F4F6N1Odd3651M1607Odd37W2YJZ5NOdd380RA3198Even39CP6MRY8Even40V10B538Even417KROR03Odd427O837O3Odd43A31Z6SUEven44692MXHDEven454P1QMS3Odd460JW209ROdd472MX23ICOdd48Lic Plt Gen 2Cell FormulasRangeFormulaB5B5=FORMULATEXT(B8)F7,D9F7=FORMULATEXT(F8)B8:B47B8=LET(n,C3,s,SEQUENCE(46,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,46,1)),LAMBDA(x,TEXTJOIN(,,x))))F8:F47F8=MAP(B8#,LAMBDA(x,EO(x)))D10D10=ROWS(B8#)Dynamic array formulas.


----------



## Xlambda (Jul 14, 2022)

Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNOPQRST1Single cell formula. Creating n random license plates , letters and numbers, min 5 max 7 chars long. More licenses should have 7 characters.2This time licenses will contain more digits than previous examples => will use ...s,SEQUENCE(66,,65)…(26 letters and 4 sequences 0-9 => sequence dimention = 26+40=66)34n 3256all 7 chars long strings7=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))))8↓↓↓random gen nr. btw 5 and 79↓↓↓Trick formula to deliver more of 7 chars than 5 or 610↓↓↓=LET(d,RANDARRAY(C4,,5,10,1),IF(d>7,7,d))11↓↓↓↓↓↓=LEFT(B12#,D12#)5,6,7 charsI11:=MAP(H12#,LAMBDA(x,EO(x)))12T4GBBO47T4GBBO418BC031←←← =LET(n,C4,
s,SEQUENCE(66,,65),
q,IF(s>90,RIGHT(s,1),CHAR(s)),
b,BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))),
d,RANDARRAY(n,,5,10,1),
LEFT(b,IF(d>7,7,d)))Odd13MU88EW47MU88EW420204Even1498X94M3798X94M31G079L8Even15K6V90607K6V906010U9Q7XOdd1695XE64G795XE64GAA142T2Even17QZF29NL6QZF29N3M6CLEven18670X4TF7670X4TFQ3190Even19S8RA7Q35S8RA76DE9QR8Even20RFHS0FI7RFHS0FI41PZ6MTasks accomplished:Even21948N6377948N637K5A8FZ8On average, licenses have more digits than letters.Even22J636P1G6J636P1517X2On average , there are more 7 chars long licenses than 5 or 6 chars.Even239D1NXP779D1NXP7H900I10Even2417FV0T8717FV0T89S6463Odd25H28ZT095H28ZTL12G788Even2631NO3V9731NO3V9G5U015BOdd271V23C8871V23C88745293COdd28H8B69226H8B6921890GEven298EX34Z168EX34ZP664K9Odd308493814684938106R77AFOdd3109XSFC8509XSF17NV3T6Even32ST82G4E7ST82G4E53MM493Odd33375DFR77375DFR7T6HZYK2Even348E06P9568E06P9E511Z6VEven351I570RS71I570RSVB676REven364992Y6D74992Y6D0023KAOdd37292F6A97292F6A95Q5328Even38NF2851S6NF285152A414Even3977J1524777J152424JJI62Even40PE45Z507PE45Z5062X6D6MEven419W5NDZ179W5NDZ1819Z8Even42848Q1457848Q1455A88LFEEven436915907769159070646957Odd44Lic Plt Gen 3Cell FormulasRangeFormulaB7B7=FORMULATEXT(B12)D10D10=FORMULATEXT(D12)F11,R11F11=FORMULATEXT(F12)B12:B43B12=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))))D12:D43D12=LET(d,RANDARRAY(C4,,5,10,1),IF(d>7,7,d))F12:F43F12=LEFT(B12#,D12#)H12:H43H12=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),b,BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))),d,RANDARRAY(n,,5,10,1),LEFT(b,IF(d>7,7,d)))R12:R43R12=MAP(H12#,LAMBDA(x,EO(x)))Dynamic array formulas.


----------



## Xlambda (Jul 17, 2022)

Found a trick to improve both solutions of Even/Odd main challenge.
*Appending a leading 1 to any number, since only last digit is relevant for an even/odd check, will not affect the outcome result.*
Why a* 1 *? Because if a string has only letters that will be ignored or replaced with "", this *1* will deliver "Odd", and this is exactly what we need.
If we append "1" as leading char to each license plate nr., recursive EO function does not need IF(n=0,"Odd"….anymore. 
Also REDUCE/SUBSTITUTE solution *delivers only digits*, => no need for an IFERROR check anymore.
*New recursive EO function* in these circumstances

```
=LAMBDA(a,
    LET(
        c, 1 & a,
        n, LEN(c),
        x, ISODD(RIGHT(c, 1)),
        IF(ISERR(x), EO(LEFT(c, n - 1)), IF(x, "Odd", "Even"))
    )
)
```
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLM1License Plate ChallengeRule: If last digit of license plate is odd nr. than "Odd", else "Even", if no digits found => "Odd"2appending 1 trick2nd solution, entire clm vector, 2 steps only31st solution, new EO=REDUCE(1&A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))4=EO([@[Plate Number]])↓↓↓=IF(ISODD(+D6#),"Odd","Even")5Plate NumberOdd/Even↓↓↓↓↓↓6123M57Odd112357OddNotes:71COU886Even11886Even - No need to extract right most digit,81D48821Odd1148821OddISODD(whole nr) will return same results91DVT189Odd11189Odd - since ISODD is a math op, no need for dbl negative1021W7454Even1217454Even1127U17Odd12717Odd=VALUETOTEXT(11)=ISODD(H12)122IGR878Even12878Even11TRUE132RDZ434Even12434Even142SWQ185Odd12185Odd - "+" sign in ISODD(+D6#), only "forces" ISODD to handle arrays153FII697Odd13697Odd163QOW866Even13866Even=ISODD(H17:H18)=ISODD(+H17:H18)173RQC229Odd13229Odd11#VALUE!TRUE183RSZ114Even13114Even122FALSE194FXL296Even14296Even204KUC981Odd14981Odd214QKB212Even14212Evencheck results, both solutions224QRG241Odd14241Odd=AND(F6#=tblChallenge[Odd/Even])234RVF139Odd14139OddTRUE245ACY568Even15568Even255IYF234Even15234Even265KFC128Even15128Even275NFD419Odd15419Odd285RXA984Even15984Even295TGB871Odd15871Odd305UEZ777Odd15777Odd315WKT763Odd15763Odd326LJT722Even16722Even336MVB117Odd16117Odd346XZR265Odd16265Odd3571F27UJOdd17127Odd36745Y4IEven17454Even3776736QEven176736Even387FOB933Odd17933Odd397JCM371Odd17371Odd40884PCXEven1884Even418JHC777Odd18777Odd428RDQ178Even18178Even438TMW513Odd18513Odd449PMH329Odd19329Odd459UGW414Even19414Even469UUL349Odd19349Odd47BEACHOdd1Odd48G97C8TEven1978Even49H1R226IEven11226Even50KL7469COdd17469Odd51RUSSOdd1Odd52S94COEven194Even53T5A5MENOdd155Odd54YELOSTNOdd1Odd55YOSEMITOdd1Odd56ChallengeCell FormulasRangeFormulaD3D3=FORMULATEXT(D6)B4,F4B4=FORMULATEXT(B6)D6:D55D6=REDUCE(1&A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))F6:F55F6=IF(ISODD(+D6#),"Odd","Even")H11,J11,H22,J16,L16H11=FORMULATEXT(H12)H12H12=VALUETOTEXT(11)J12J12=ISODD(H12)J17J17=ISODD(H17:H18)L17:L18L17=ISODD(+H17:H18)H23H23=AND(F6#=tblChallenge[Odd/Even])B6:B55B6=EO([@[Plate Number]])Dynamic array formulas.


----------



## Xlambda (Jul 17, 2022)

*3rd solution*, and still unconventional (without using MID)
Excel-Challenge-License-Plates.xlsxABCDEFGHIJ1License Plate Challenge233rd solution4=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&A6,CHAR(SEQUENCE(26,,65))))),"Odd","Even")5Plate NumberOdd/Even6123M57Odd71COU886EvenIf strings with lowercase values, we can use UPPER81D48821Odd91DVT189Odd=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&UPPER(D11),CHAR(SEQUENCE(26,,65))))),"Odd","Even")1021W7454Even↓↓↓1127U17OddzAOdd122IGR878Evenxa34dEven132RDZ434EvenDsTrOdd142SWQ185Oddz357wP300fGEven153FII697Odd2uyfoTYFIIYTFEven163QOW866Even2VnM2223lLkJHOdd173RQC229Odd183RSZ114Even194FXL296Even204KUC981Odd214QKB212Even224QRG241Odd234RVF139Odd245ACY568Even255IYF234Even265KFC128Even275NFD419Odd285RXA984Even295TGB871Odd305UEZ777Odd315WKT763Odd326LJT722Even336MVB117Odd346XZR265Odd3571F27UJOdd36745Y4IEven3776736QEven387FOB933Odd397JCM371Odd40884PCXEven418JHC777Odd428RDQ178Even438TMW513Odd449PMH329Odd459UGW414Even469UUL349Odd47BEACHOdd48G97C8TEven49H1R226IEven50KL7469COdd51RUSSOdd52S94COEven53T5A5MENOdd54YELOSTNOdd55YOSEMITOdd56ChallengeCell FormulasRangeFormulaB4B4=FORMULATEXT(B6)D9D9=FORMULATEXT(F11)F11:F16F11=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&UPPER(D11),CHAR(SEQUENCE(26,,65))))),"Odd","Even")B6:B55B6=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&A6,CHAR(SEQUENCE(26,,65))))),"Odd","Even")


----------



## Xlambda (Jul 15, 2021)

ARF *!! recursive !!* *DIY* *A*rray *R*ecursive *F*unction kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
*ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))*
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
*ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))*
AGG study.xlsxABCDEFGHIJKLMNOPQRS1Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)2Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible3To append 2D arrays we already have APPENDNHV4Writing the recursive function following the syntax draft, function name, let's define APP:5APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))6The appending "engine" functionality is extremely simple IF(s=j,x,ai)7Is equivalent with this :8=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))9a10ba1=APP((C11:D11,C14:E14,C17:D17),,)112a2a2#N/A12b3413a212#N/A14b341516a317121819General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array20a121a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))22xw223atwx23txa2425so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))26Now let's define our specific recursive function (ASU) using the kit syntax27ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))28a229a2-1=ASU((B21:D23,B29:D33,B36:C37),,)30qac23atwx31dc2-123acdq32-13-12q332dd3435a3Other function on minisheet36q2AFLAT372q3839This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.40It will be nice to see others function creations posted here!!!41ARF postCell FormulasRangeFormulaA8,G29,G21,G10A8=FORMULATEXT(A9)A9:A11A9=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))G11:I13G11=APP((C11:D11,C14:E14,C17:D17),,)G22:L22G22=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))G30:M32G30=ASU((B21:D23,B29:D33,B36:C37),,)Dynamic array formulas.


----------



## Xlambda (Jul 17, 2022)

I have never used for any of my recursive functions a trick I've learned from Mynda long time ago.
Excel LAMBDA Recursion + a Trick for Evaluating in a Cell
*Using this trick, we do not need to define a recursive lambda, it calls itself in the cell.*
Here are some examples, basic simple recursive lambdas, to visualize how it works, various scenarios.
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKL12Basic recursive lambda that reverses any string. (1 variable, "a")34defined REV(a)=LAMBDA(a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&REV(LEFT(a,n-1)))))5check6=REV(B7)=TEXTJOIN(,,MID(B7,SEQUENCE(,LEN(B7),LEN(B7),-1),1))7aBcD,:123321:,DcBa321:,DcBa89to make it call itself in a cell, not defined1011=LET(a,B7,fn,LAMBDA(y,a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&y(y,LEFT(a,n-1))))),fn(fn,a))12321:,DcBa131415Recursive lambda that adds all digits. (2 variables, "a" and "s")1617defined ADDIG(a,[s ])=LAMBDA(a,[s ],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,ADDIG(LEFT(a,n-1),x))))18check19=ADDIG(B20)=SUM(--MID(B20,SEQUENCE(LEN(B20)),1))20text→3285120930302122=ADDIG(B23)23number→32851209302425in cell, not defined =LET(a,B20,fn,LAMBDA(y,a,[s ],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))263027in cell recursive 1Cell FormulasRangeFormulaG6,D22,G19,D19,D11,D6G6=FORMULATEXT(G7)D7D7=REV(B7)G7G7=TEXTJOIN(,,MID(B7,SEQUENCE(,LEN(B7),LEN(B7),-1),1))D12D12=LET(a,B7,fn,LAMBDA(y,a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&y(y,LEFT(a,n-1))))),fn(fn,a))D20,D23D20=ADDIG(B20)G20G20=SUM(--MID(B20,SEQUENCE(LEN(B20)),1))D26D26=LET(a,B20,fn,LAMBDA(y,a,[s],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))


----------



## Xlambda (Jul 17, 2022)

Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNOPQR12Recursive lambda that extracts only the digits. (2 variables, double self calling)34defined EXDIG(a,[d])=LAMBDA(a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,EXDIG(LEFT(a,n-1),x&r),EXDIG(LEFT(a,n-1),r)))))56=EXDIG(B7)71g23T(45)Pz%612345689in cell, not defined=LET(a,B7,fn,LAMBDA(y,a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,y(y,LEFT(a,n-1),x&r),y(y,LEFT(a,n-1),r))))),fn(fn,a))10123456111213Recursive lambda that extracts and adds digits1415defined ADDEXDIG(a,[s ])=LAMBDA(a,[s ],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,ADDEXDIG(LEFT(a,n-1),x))))16checked with nested recursive lambdas17=ADDEXDIG(B18)=ADDIG(EXDIG(B7))181g23T(45)Pz%621211920in cell, not defined =LET(a,B18,fn,LAMBDA(y,a,[s ],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))2121222324As we see in this various scenarios, the algorithm is straight forward. If a defined recursive lambda is called RL(a,b), we construct a 3 parts LET2526RL(a,b)=LAMBDA(a,b,LET(…..a1….a2…IF(exit cond.,acumulator,RL(a1,b1))))2728 =LET(a,cell,fn,LAMBDA(y,a,b,LET(….a1…a2….IF(exit cond.,acumulator,y(y,a1,b1)))),fn(fn,a,b))2930 - 1st part input variable, 2nd part lambda variable, 3rd part calling lambda variable31in cell recursive 2Cell FormulasRangeFormulaD6,G17,D17,D9D6=FORMULATEXT(D7)D7D7=EXDIG(B7)D10D10=LET(a,B7,fn,LAMBDA(y,a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,y(y,LEFT(a,n-1),x&r),y(y,LEFT(a,n-1),r))))),fn(fn,a))D18D18=ADDEXDIG(B18)G18G18=ADDIG(EXDIG(B7))D21D21=LET(a,B18,fn,LAMBDA(y,a,[s],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))


----------



## Xlambda (Jul 17, 2022)

All *recursive* functions:

*REV(a) Reverse any string*
*a*: single value/cell reference, any string

```
=LAMBDA(a, LET(n, LEN(a), x, RIGHT(a, 1), IF(n = 0, a, x & REV(LEFT(a, n - 1)))))
```

*ADDIG(a,[sm]) Adds all digits of a number or number as text*
*a*: single value/cell reference, number, or numeric text value (only digits)
*[sm]*: always omitted, (initial value always 0) after 1st iteration is used as an "acumulator" that stores the sum of digits through iterations.

```
=LAMBDA(a, [sm],
    LET(n, LEN(a), x, sm + RIGHT(a, 1), IF(n = 0, sm, ADDIG(LEFT(a, n - 1), x)))
)
```

*EXDIG(a,[d]) Extracts all the digits in a string*
*a*: single value/cell reference, any string
*[d]*: always omitted (initial value empty string ""), after 1st iteration is used as an "acumulator" that appends only the digits, ignoring text

```
=LAMBDA(a, [d],
    LET(
        r, IF(d = "", "", d),
        n, LEN(a),
        x, RIGHT(a, 1),
        in, ISNUMBER(--x),
        IF(n = 0, d, IF(in, EXDIG(LEFT(a, n - 1), x & r), EXDIG(LEFT(a, n - 1), r)))
    )
)
```

*ADDEXDIG(a,[sm]) Add Extracted Digits *(have used sm instead of previous s because [ s ] triggers strikethrough format, straight line cutting through the center)
*a*: single value/cell reference, any string
*[sm]*: always omitted, (initial value always 0) after 1st iteration is used as an "acumulator" that stores the sum of digits through iterations, ignoring text

```
=LAMBDA(a, [sm],
    LET(
        n, LEN(a),
        x, sm + IFERROR(--RIGHT(a, 1), 0),
        IF(n = 0, sm, ADDEXDIG(LEFT(a, n - 1), x))
    )
)
```
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLMNO12extracting digitsadding digitsextracting and adding digits3=MAP(B4:B6,LAMBDA(x,EXDIG(x)))=MAP(D4#,LAMBDA(x,ADDIG(x)))=MAP(B4:B6,LAMBDA(x,ADDEXDIG(x)))4e7R%-;6gH95>28j7695283737590876xRTy2@#579087625744446b9999K9888888x7779999988888877711411478reversed9=MAP(B4:B6,LAMBDA(x,REV(x)))=MAP(B10:B12,LAMBDA(x,EXDIG(x)))=MAP(D10#,LAMBDA(x,ADDIG(x)))=MAP(B10:B12,LAMBDA(x,ADDEXDIG(x)))10j82>59Hg6;-%R7e82596737371175#@2yTRx6780975267809444412777x8888889K9999b777888888999991141141314sum of digits in reversed order deliver same results1516Note:17EXDIG keeps the result in text format for not allowing Excel to turn it into scientific format with E when many digits are extracted.1819=--D6209.99999E+1321in cell recursive 3Cell FormulasRangeFormulaD3,G3,K3,D19,B9,D9,G9,K9D3=FORMULATEXT(D4)D4:D6,D10:D12D4=MAP(B4:B6,LAMBDA(x,EXDIG(x)))G4:G6,G10:G12G4=MAP(D4#,LAMBDA(x,ADDIG(x)))K4:K6,K10:K12K4=MAP(B4:B6,LAMBDA(x,ADDEXDIG(x)))B10:B12B10=MAP(B4:B6,LAMBDA(x,REV(x)))D20D20=--D6Dynamic array formulas.


----------



## Xlambda (Jul 17, 2022)

*4th solution for even/odd challenge, in cell recursive lambda, not defined, and 5th, a formula that calls EXDIG.
In total, 5 solutions as alternatives to using MID*
Excel-Challenge-License-Plates.xlsxABCDEFGHIJKLM1License Plate Challenge24th solution, in cell recursive lambda3=LET(a,A6,fn,LAMBDA(y,a,LET(c,1&a,n,LEN(c),x,ISODD(RIGHT(c,1)),IF(ISERR(x),y(y,LEFT(c,n-1)),IF(x,"Odd","Even")))),fn(fn,a))4↓↓5Plate NumberOdd/Even5th →=LET(a,A6:A55,m,MAP(a,LAMBDA(x,EXDIG(x))),IF(IFERROR(ISODD(m),1),"Odd","Even"))6123M57OddOdd71COU886EvenEvencheck81D48821OddOdd=AND(tblChallenge[Odd/Even]=E6#)91DVT189OddOddTRUE1021W7454EvenEven1127U17OddOdd122IGR878EvenEven132RDZ434EvenEven142SWQ185OddOdd153FII697OddOdd163QOW866EvenEven173RQC229OddOdd183RSZ114EvenEven194FXL296EvenEven204KUC981OddOdd214QKB212EvenEven224QRG241OddOdd234RVF139OddOdd245ACY568EvenEven255IYF234EvenEven265KFC128EvenEven275NFD419OddOdd285RXA984EvenEven295TGB871OddOdd305UEZ777OddOdd315WKT763OddOdd326LJT722EvenEven336MVB117OddOdd346XZR265OddOdd3571F27UJOddOdd36745Y4IEvenEven3776736QEvenEven387FOB933OddOdd397JCM371OddOdd40884PCXEvenEven418JHC777OddOdd428RDQ178EvenEven438TMW513OddOdd449PMH329OddOdd459UGW414EvenEven469UUL349OddOdd47BEACHOddOdd48G97C8TEvenEven49H1R226IEvenEven50KL7469COddOdd51RUSSOddOdd52S94COEvenEven53T5A5MENOddOdd54YELOSTNOddOdd55YOSEMITOddOdd56ChallengeCell FormulasRangeFormulaB3B3=FORMULATEXT(B6)E5,G8E5=FORMULATEXT(E6)E6:E55E6=LET(a,A6:A55,m,MAP(a,LAMBDA(x,EXDIG(x))),IF(IFERROR(ISODD(m),1),"Odd","Even"))G9G9=AND(tblChallenge[Odd/Even]=E6#)B6:B55B6=LET(a,A6,fn,LAMBDA(y,a,LET(c,1&a,n,LEN(c),x,ISODD(RIGHT(c,1)),IF(ISERR(x),y(y,LEFT(c,n-1)),IF(x,"Odd","Even")))),fn(fn,a))Dynamic array formulas.


----------



## Xlambda (Aug 14, 2022)

Sunday's shorts.
Recursive functions can deal with multiple arguments, ("accumulators" or counters)
Simple basic example to show the technique of replacing recursive functionality using REDUCE with 2 "accumulator" arguments (array of accumulators) and an exit condition.
*Task: Find the index number of a vector and the closest running total value over a given amount.
RTL(a,[rt],[i ]) *Running Total Limit, *recursive!!
a: *column vector
*[rt]*: "accumulator" of running total
*[i ]*: index count

```
=LAMBDA(a,[rt],[i ],LET(j,IF(i,i,1),x,rt+INDEX(a,j),IF(x>=$C$3,HSTACK(j,x),RTL(a,x,j+1))))
```
Book1ABCDEFGHIJKLMNOPQRST12limit2. reduce, array of accumulators32000=REDUCE({0,0},C8:C37,LAMBDA(v,i,LET(x,INDEX(v,2),HSTACK(INDEX(v,1)+IF(x<C3,1,0),x+IF(x<C3,i,0)))))4↓↓↓3. classic5↓↓↓=LET(a,C8:C37,l,C3,s,SCAN(0,a,LAMBDA(v,i,(v+i))),x,XMATCH(l,s,1),HSTACK(x,INDEX(s,x)))61. recursive↓↓↓↓↓↓checking7=RTL(C8:C38)↓↓↓↓↓↓=SCAN(0,C8:C37,LAMBDA(v,i,v+i))81982021072021072021071989215522531037533281141784506125315537136265391471967735158568791169128991917101041010231811601110831912182121265201311131276211418214145822159415155223161531617052417941717992518801818792619961919752720132202107282120021230729221552224623023482325103124542425643225140252704332692627133427117272830352897282927362916829309537302830312338Sheet1Cell FormulasRangeFormulaH3H3=FORMULATEXT(H8)K5K5=FORMULATEXT(K8)E7,P7E7=FORMULATEXT(E8)B8:B37,O8:O37B8=SEQUENCE(30)E8:F8E8=RTL(C8:C38)H8:I8H8=REDUCE({0,0},C8:C37,LAMBDA(v,i,LET(x,INDEX(v,2),HSTACK(INDEX(v,1)+IF(x<C3,1,0),x+IF(x<C3,i,0)))))K8:L8K8=LET(a,C8:C37,l,C3,s,SCAN(0,a,LAMBDA(v,i,(v+i))),x,XMATCH(l,s,1),HSTACK(x,INDEX(s,x)))P8:P37P8=SCAN(0,C8:C37,LAMBDA(v,i,v+i))Dynamic array formulas.


----------



## Xlambda (Aug 14, 2022)

Structural differences btwn solutions.
1. recursive: when limit condition is met, function exits iterations, efficient but limited nr. of recursive iterations, not good for large vectors
2. reduce: when limit condition is met, array of accumulators stop accumulating, iterations will have to continue, no iterations limit other than excel real estate limits.
3. classic: even if this example has no recursive approach solution, the technique used by *reduce with an array of accumulators simulating an exit condition* could be useful for more complex scenarios.


----------



## Xlambda (Nov 20, 2022)

*Sunday fun with lambda "short" formulas, inspired from YT shorts 🤸‍♂️*








From Wikipedia:
*6174* is known as *Kaprekar's constant* after the Indian mathematician D. R. Kaprekar. This number is renowned for the following rule:
Take any four-digit number, using at least two different digits (leading zeros are allowed).
Arrange the digits in descending and then in ascending order to get two four-digit numbers, adding leading zeros if necessary.
Subtract the smaller number from the bigger number.
Go back to step 2 and repeat.
The above process, known as Kaprekar's routine, will always reach its fixed point, 6174, in at most 7 iterations. Once 6174 is reached, the process will continue yielding 7641 – 1467 = 6174. For example, choose 1495:
9541 – 1459 = 8082
8820 – 0288 = 8532
8532 – 2358 = 6174
7641 – 1467 = 6174

*Recursive* function to check it (1st draft):
*KPRKR(n )*

```
=LAMBDA(n,LET(a,MID(n,SEQUENCE(LEN(n)),1),x,CONCAT(SORT(a,,-1)),y,CONCAT(SORT(a)),z,x-y,IF(z=n,n,KPRKR(z))))
```
Kaprekar.xlsxABCDEFGHIJKLMNOPQRSTUVWXY12=KPRKR(1495)startCF values <> 617436174100045=KPRKR(1482)=SEQUENCE(10,20,1000)66174100010011002100310041005100610071008100910101011101210131014101510161017101810197102010211022102310241025102610271028102910301031103210331034103510361037103810398=KPRKR(8273)1040104110421043104410451046104710481049105010511052105310541055105610571058105996174106010611062106310641065106610671068106910701071107210731074107510761077107810791010801081108210831084108510861087108810891090109110921093109410951096109710981099111100110111021103110411051106110711081109111011111112111311141115111611171118111912112011211122112311241125112611271128112911301131113211331134113511361137113811391311401141114211431144114511461147114811491150115111521153115411551156115711581159141160116111621163116411651166116711681169117011711172117311741175117611771178117915118011811182118311841185118611871188118911901191119211931194119511961197119811991617=MAP(E6#,KPRKR)18061746174617461746174617461746174617461740617461746174617461746174617461741961746174617461746174617461746174617461746174617461746174617461746174617461746174206174617461746174617461746174617461746174617461746174617461746174617461746174617421617461746174617461746174617461746174617461746174617461746174617461746174617461742261746174617461746174617461746174617461746174617461746174617461746174617461746174236174061746174617461746174617461746174000617461746174617461746174617424617406174617461746174617461746174617461746174617461746174617461746174617461742561746174617461746174617461746174617461746174617461746174617461746174617461746174266174617461746174617461746174617461746174617461746174617461746174617461746174617427617461746174617461746174617461746174617461746174617461746174617461746174617461742829K 1Cell FormulasRangeFormulaB2,E17,B8,E5,B5B2=FORMULATEXT(B3)B3B3=KPRKR(1495)B6B6=KPRKR(1482)E6:X15E6=SEQUENCE(10,20,1000)B9B9=KPRKR(8273)E18:X27E18=MAP(E6#,KPRKR)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueE18:X27Expression=E18=0textNOE6:X15Expression=E18=0textNO


----------



## Xlambda (Nov 20, 2022)

To deal with the "0"'s there is an algorithm rule:
The only four-digit numbers for which Kaprekar's routine does not reach 6174 are repdigits such as 1111, which give the result 0000 after a single iteration. All other four-digit numbers eventually reach 6174 if leading zeros are used to keep the number of digits at 4. For numbers with three identical numbers and a fourth number that is one number higher or lower (such as 2111), it is essential to treat 3-digit numbers with a leading zero; for example: 2111 – 1112 = 0999; 9990 – 999 = 8991; 9981 – 1899 = 8082; 8820 – 288 = 8532; 8532 – 2358 = 6174.
Therefore, here is draft nr. 2 of *recursive* function:
*KPK(n )*

```
=LAMBDA(n,LET(a,MID(n,SEQUENCE(LEN(n)),1),x,CONCAT(SORT(a,,-1)),y,CONCAT(SORT(a)),z,x-y,v,IF(LEN(z)<LEN(n),z*10,z),IF(v=n,n,KPK(v))))
```
Kaprekar.xlsxABCDEFGHIJKLMNOPQRSTUVWX12startCF values <> 61743100045=SEQUENCE(10,20,B3)6100010011002100310041005100610071008100910101011101210131014101510161017101810197102010211022102310241025102610271028102910301031103210331034103510361037103810398104010411042104310441045104610471048104910501051105210531054105510561057105810599106010611062106310641065106610671068106910701071107210731074107510761077107810791010801081108210831084108510861087108810891090109110921093109410951096109710981099111100110111021103110411051106110711081109111011111112111311141115111611171118111912112011211122112311241125112611271128112911301131113211331134113511361137113811391311401141114211431144114511461147114811491150115111521153115411551156115711581159141160116111621163116411651166116711681169117011711172117311741175117611771178117915118011811182118311841185118611871188118911901191119211931194119511961197119811991617IMP Note: KPK is a single argument function => as lambda helper function in MAP, instead of LAMBDA(x,KPX(x)) we can write simplified:18=MAP(B6#,KPK)196174617461746174617461746174617461746174617461746174617461746174617461746174617420617461746174617461746174617461746174617461746174617461746174617461746174617461742161746174617461746174617461746174617461746174617461746174617461746174617461746174226174617461746174617461746174617461746174617461746174617461746174617461746174617423617461746174617461746174617461746174617461746174617461746174617461746174617461742461746174617461746174617461746174617461746174061746174617461746174617461746174256174617461746174617461746174617461746174617461746174617461746174617461746174617426617461746174617461746174617461746174617461746174617461746174617461746174617461742761746174617461746174617461746174617461746174617461746174617461746174617461746174286174617461746174617461746174617461746174617461746174617461746174617461746174617429K 2Cell FormulasRangeFormulaB5,B18B5=FORMULATEXT(B6)B6:U15B6=SEQUENCE(10,20,B3)B19:U28B19=MAP(B6#,KPK)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB19:U28Expression=B19=0textNOB6:U15Expression=B19=0textNO


----------



## Xlambda (Nov 20, 2022)

Side off topic Note: I know that these formulas have no real-life use, but at least, I hope they are fun, and the techniques used are useful enough to be shared. Lambda fitness training for the real world. 😉🙏🏋️
Now, let's check the new function for all 9000 numbers between 1000 and 9999 to see if delivers only 6174 (except the nr. with repeated digits like 1111,2222,...9999)
Kaprekar.xlsxABCDEFGHIJKLMN1Checking all values btwn 1000 and 9999 (all 9000 values)2all values = 0all values = 61743=SEQUENCE(9000,,1000)=MAP(B4#,LAMBDA(x,KPK(x)))=FILTER(B4#,D4#=0)=FILTER(B4#,D4#=6174)4100061741111100051001617422221001=ROWS(F4#)+ROWS(H4#)61002617433331002900071003617444441003 =>81004617455551004all values are 6174, except the expected 9 0's values910056174666610051010066174777710061110076174888810071210086174999910081310096174100914101061741010151011617410111610126174101217101361741013181014617410141910156174101520101661741016211017617410172210186174101823101961741019241020617410202510216174102126102261741022271023617410232810246174102429102561741025301026617410263110276174102732102861741028331029617410293410306174103035103161741031361032617410323710336174103338103461741034391035617410354010366174103641103761741037421038617410384310396174103944104061741040451041617410414610426174104247104361741043481044617410444910456174104550104661741046511047617410475210486174104853104961741049541050617410505510516174105156105261741052571053617410535810546174105459105561741055601056617410566110576174105762105861741058631059617410596410606174106065106161741061K 3Cell FormulasRangeFormulaB3,D3,F3,H3,J5B3=FORMULATEXT(B4)B4:B9003B4=SEQUENCE(9000,,1000)D4:D9003D4=MAP(B4#,LAMBDA(x,KPK(x)))F4:F12F4=FILTER(B4#,D4#=0)H4:H8994H4=FILTER(B4#,D4#=6174)J6J6=ROWS(F4#)+ROWS(H4#)Dynamic array formulas.


----------

