ARF

=ARF(a,ai,i)
a
(a1,a2,...,an) non-adjacent ranges/arrays on same sheet, enclosed in parentheses
ai
always omitted (vector carrier)
i
always omitted (vector carrier)
DIY Array Recursive Function kit
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
ARF !! recursive !! DIY Array Recursive Function 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.xlsx
ABCDEFGHIJKLMNOPQRS
1Base 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 possible
3To append 2D arrays we already have APPENDNHV
4Writing 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))
9a
10ba1=APP((C11:D11,C14:E14,C17:D17),,)
112a2a2#N/A
12b34
13a212#N/A
14b34
15
16a3
1712
18
19General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array
20a1
21a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
22xw223atwx
23txa
24
25so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))
26Now let's define our specific recursive function (ASU) using the kit syntax
27ASU(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))))
28a2
29a2-1=ASU((B21:D23,B29:D33,B36:C37),,)
30qac23atwx
31dc2-123acdq
32-13-12q
332dd
34
35a3Other function on minisheet
36q2AFLAT
372q
38
39This 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!!!
41
ARF post
Cell Formulas
RangeFormula
A8,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.
 
Upvote 0
Same functions, this time using REDUCE, not recursive, both call PL
PSC(n ) n: N>=2 PaSCal's triangle Line, returns single line n'th
Excel Formula: 
=LAMBDA(n,REDUCE(1,SEQUENCE(n-1),LAMBDA(v,i,IF(i=1,{1,1},PL(v)))))
PSCT(n ) n: N>=2 PaSCal's Triangle, returns entire triangle
Excel Formula: 
=LAMBDA(n,IFNA(REDUCE(1,SEQUENCE(n-1),LAMBDA(v,i,VSTACK(v,IF(i=1,{1,1},PL(TAKE(v,-1)))))),""))
Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2=PSC(2)
311
4
5=PSC(23)
6122231154073152633474613170544319770497420646646705432646646497420319770170544746132633473151540231221
7
8=PSCT(2)
91
1011
11
12=PSCT(23)
131
1411
15121
161331
1714641
1815101051
191615201561
20172135352171
2118285670562881
22193684126126843691
231104512021025221012045101
241115516533046246233016555111
251126622049579292479249522066121
2611378286715128717161716128771528678131
2711491364100120023003343230032002100136491141
2811510545513653003500564356435500530031365455105151
29116120560182043688008114401287011440800843681820560120161
301171366802380618812376194482431024310194481237661882380680136171
31118153816306085681856431824437584862043758318241856485683060816153181
321191719693876116282713250388755829237892378755825038827132116283876969171191
331201901140484515504387607752012597016796018475616796012597077520387601550448451140190201
34121210133059852034954264116280203490293930352716352716293930203490116280542642034959851330210211
35122231154073152633474613170544319770497420646646705432646646497420319770170544746132633473151540231221
36
Sheet4
Cell Formulas
RangeFormula
B2,B12,B8,B5B2=FORMULATEXT(B3)
B3:C3B3=PSC(2)
B6:X6B6=PSC(23)
B9:C10B9=PSCT(2)
B13:X35B13=PSCT(23)
Dynamic array formulas.
 
For fun, a function to write binomial powers expression, (x+y)^n
XYN(n ) n:N>=1 calls PSC
Excel Formula: 
=LAMBDA(n,
    LET(
        k, n + 1,
        s, SEQUENCE(, k),
        y, s - 1,
        x, k - s,
        a, "x" & x,
        b, IF(x, IF(x = 1, "x", a), ""),
        c, "y" & y,
        d, IF(y, IF(y = 1, "y", c), ""),
        p, PSC(k),
        q, IF(p = 1, "", p & "·"),
        TEXTJOIN("+", , CONCATENATE(q, b, d))
    )
)
Book1
ABCDE
1
2n
31x+y
42x2+2·xy+y2
53x3+3·x2y+3·xy2+y3
64x4+4·x3y+6·x2y2+4·xy3+y4
75x5+5·x4y+10·x3y2+10·x2y3+5·xy4+y5
86x6+6·x5y+15·x4y2+20·x3y3+15·x2y4+6·xy5+y6
97x7+7·x6y+21·x5y2+35·x4y3+35·x3y4+21·x2y5+7·xy6+y7
108x8+8·x7y+28·x6y2+56·x5y3+70·x4y4+56·x3y5+28·x2y6+8·xy7+y8
119x9+9·x8y+36·x7y2+84·x6y3+126·x5y4+126·x4y5+84·x3y6+36·x2y7+9·xy8+y9
1210x10+10·x9y+45·x8y2+120·x7y3+210·x6y4+252·x5y5+210·x4y6+120·x3y7+45·x2y8+10·xy9+y10
1311x11+11·x10y+55·x9y2+165·x8y3+330·x7y4+462·x6y5+462·x5y6+330·x4y7+165·x3y8+55·x2y9+11·xy10+y11
14
Sheet5
Cell Formulas
RangeFormula
B3:B13B3=SEQUENCE(11)
D3:D13D3=XYN(B3)
Dynamic array formulas.
 
Simplest way, functions without recursion, using COMBIN:
CPSC(n ) n: N>=1 Combinatorics Pascal's triangle n'th row
Excel Formula: 
=LAMBDA(n,HSTACK(1,COMBIN(n,SEQUENCE(,n))))
CPSC(n ) n: N>=1 Combinatorics Pascal's entire triangle
Excel Formula: 
=LAMBDA(n,IFNA(REDUCE({1,1},SEQUENCE(n-1)+1,LAMBDA(v,i,VSTACK(v,HSTACK(1,COMBIN(i,SEQUENCE(,i)))))),""))
Imp Note: For very large values, this method will lose precision, the first method, using PL, since has an adding op, therefore we can use functions designed for these scenarios like: LADD
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Obs: Seen different representations, this is the triangle with no top "angle" => First row of triangle => 1,1
2
3n
4111
52121
631331
7414641
8515101051
961615201561
107172135352171
11818285670562881
129193684126126843691
13101104512021025221012045101
14111115516533046246233016555111
15121126622049579292479249522066121
161311378286715128717161716128771528678131
171411491364100120023003343230032002100136491141
181511510545513653003500564356435500530031365455105151
1916116120560182043688008114401287011440800843681820560120161
20171171366802380618812376194482431024310194481237661882380680136171
2118118153816306085681856431824437584862043758318241856485683060816153181
22191191719693876116282713250388755829237892378755825038827132116283876969171191
23201201901140484515504387607752012597016796018475616796012597077520387601550448451140190201
24
25=CPSCT(20)
2611
27121
281331
2914641
3015101051
311615201561
32172135352171
3318285670562881
34193684126126843691
351104512021025221012045101
361115516533046246233016555111
371126622049579292479249522066121
3811378286715128717161716128771528678131
3911491364100120023003343230032002100136491141
4011510545513653003500564356435500530031365455105151
41116120560182043688008114401287011440800843681820560120161
421171366802380618812376194482431024310194481237661882380680136171
43118153816306085681856431824437584862043758318241856485683060816153181
441191719693876116282713250388755829237892378755825038827132116283876969171191
451201901140484515504387607752012597016796018475616796012597077520387601550448451140190201
46
Sheet6
Cell Formulas
RangeFormula
B4:B23B4=SEQUENCE(20)
D4:E4,D23:X23,D22:W22,D21:V21,D20:U20,D19:T19,D18:S18,D17:R17,D16:Q16,D15:P15,D14:O14,D13:N13,D12:M12,D11:L11,D10:K10,D9:J9,D8:I8,D7:H7,D6:G6,D5:F5D4=CPSC(B4)
D25D25=FORMULATEXT(D26)
D26:X45D26=CPSCT(20)
Dynamic array formulas.
 
Dealing with Pascal's Triangle large numbers.
Introducing L2ADD(a,b) can add 2 arrays, 2 vectors, 2 values of large or regular numbers calls LADD
Excel Formula: 
=LAMBDA(a,b,MAP(a,b,LAMBDA(x,y,LADD(VSTACK(x,y)))))
Adapting PL tool function (Pascai's triangle Line for large numbers =>LPL(x) calls L2ADD
Excel Formula: 
=LAMBDA(x,HSTACK(1,L2ADD(DROP(x,,-1),DROP(x,,1)),1))
LPSC(n ) n: N>=1 Large values Pascal's triangle n'th line(row) calls LPL
Excel Formula: 
=LAMBDA(n,REDUCE(1,SEQUENCE(n),LAMBDA(v,i,IF(i=1,{1,1},LPL(v)))))
Book1.xlsx
ABCDEFGHI
1Recap LADD, L2ADD fundamentals
2LADD adds any array of numbers in numbers format , no decimals or scientific notation (N numbers set), or text string digits any size => result always as text string.
3
4text string( full precision )number format (precision lost)
52145503987654567890987456789765234567865434568765456765456780000
65443256745667887654323456654312345678765434567876789876534565434567876500000
7
8=LADD(B5:C6)=SUM(B5:C6)=LADD(E5:F6)
95443258958954432589589876942000023332222202509961760468887722221
10
11L2ADD, large adding 2 values or 2 compatible arrays/vectors
12
13adding 2 rows
14=L2ADD(E5:F5,E6:F6)
1587694200002333222220246663086913555444222143330891333333280000
16
17checking results
18=LADD(E15#)
19876942000023332222202509961760468887722221
20
21adding 2 columns
22=L2ADD(E5:E6,F5:F6)
23398765456789098745687741980222243323456
24876543234566543123456822219780246644398765
25
26checking results
27=LADD(E23#)
28876942000023332222202509961760468887722221
29
30adding 2 2D arrays
31=L2ADD(B5:C6,E5:F6)
323987654567890987456789765234567865649118765456765456780000
3387654323456654312345678765434573320046622134565434567876500678
34
Sheet8
Cell Formulas
RangeFormula
B8:C8,E31,E27,E22,E18,E14,E8B8=FORMULATEXT(B9)
B9,E9B9=LADD(B5:C6)
C9C9=SUM(B5:C6)
E15:F15E15=L2ADD(E5:F5,E6:F6)
E19E19=LADD(E15#)
E23:E24E23=L2ADD(E5:E6,F5:F6)
E28E28=LADD(E23#)
E32:F33E32=L2ADD(B5:C6,E5:F6)
Dynamic array formulas.


Easy way to visualize the "precision" differences between LPSC(n ) vs CPSC(n ) (Large number adding vs COMBIN method) (Trailing 0's => no precision)

Book1.xlsx
ABCDEFGHIJKL
1Set up: Cell format as numbers (not scientific), no decimals, left aligned, wrapped results for easy comparison. n=99 has 100 clms=> can be wrapped 10x10
2
3=LPSC(99)
41994851156849376437671523144112052925614887031544171200862756173103094564415579278510796
5
6=WRAPROWS(B4#,10)
719948511568493764376715231441120529256148870315441712008627561731030945644
8155792785107961260505261328049243705249738966186171974825304380007707024982962153377006474903441130522928399324306551961194453787749425144898858450330806107196674080761936594
9428786696323047746376161305471473908437922457190121704385718899761914625813581608850122460629817430084280253876181889452290252840761628517685364210719623706172139966783656972342705742835991458654650030981476728811701946483283447189128
102056063787512766137677463245764000431735762419272568972485009174384951409542071974439261051023992255736933832735036157870102614077577117936495721758761997572631265410932572757113244012912215461861492118103065872468835154303717135058921273929125498493658321124600506947888
118247740487481686900760421832118686997258882811498747533681639010914527429301649370703221726423750712434928840495368276518120463612808185242668323379665916777489877819632612839674339023040098565708730672447391482600239409357992069284846741061502593601378247417250445672272782096667406248628
125044567227278209666740624862848467410615025936013782474172447391482600239409357992069283967433902304009856570873067233796659167774898778196326128276518120463612808185242668322172642375071243492884049536816390109145274293016493707032118686997258882811498747533688247740487481686900760421832
135498493658321124600506947888351543037171350589212739291221546186149211810306587246881265410932572757113244012912711793649572175876199757263383273503615787010261407757197443926105102399225573693972485009174384951409542074576400043173576241927256820560637875127661376774632
14881170194648328344718912835991458654650030981476721399667836569723427057428517685364210719623706172181889452290252840761628606298174300842802538761914625813581608850122457190121704385718899761613054714739084379224428786696323047746376
15107196674080761936594251448988584503308065519611944537877494113052292839932430621533770064749034438000770702498296618617197482530492437052497389612605052613280415579278510796
1617310309456441712008627561488703154411205292567152314437643761568494851991
17
18=CPSC(99)
1919948511568493764376715231441120529256148870315441.71201E+111.73103E+121.55793E+13
20
21=WRAPROWS(B19#,10)
2219948511568493764376715231441120529256148870315441712008627561731030945644
23155792785107961260505261328049243705249738966186171974825300380007707024983002153377006474900001130522928399320000551961194453788000025144898858450300000107196674080762000000
24428786696323048000000161305471473908000000057190121704385700000001914625813581610000000060629817430084300000000181889452290253000000000517685364210720000000000139966783656972000000000035991458654650000000000008811701946483280000000000
252056063787512760000000000045764000431735800000000000972485009174386000000000001974439261051020000000000003832735036157870000000000007117936495721760000000000001265410932572760000000000000215461861492118000000000000035154303717135100000000000005498493658321130000000000000
268247740487481690000000000000118686997258883000000000000001639010914527430000000000000021726423750712400000000000000276518120463613000000000000003379665916777490000000000000039674339023040100000000000000447391482600240000000000000004846741061502590000000000000050445672272782100000000000000
275044567227278210000000000000048467410615025900000000000000447391482600240000000000000003967433902304010000000000000033796659167774900000000000000276518120463613000000000000002172642375071240000000000000016390109145274300000000000000118686997258883000000000000008247740487481690000000000000
285498493658321130000000000000351543037171351000000000000021546186149211800000000000001265410932572760000000000000711793649572176000000000000383273503615787000000000000197443926105102000000000000972485009174386000000000004576400043173580000000000020560637875127600000000000
29881170194648328000000000035991458654650000000000001399667836569720000000000517685364210720000000000181889452290253000000000606298174300843000000001914625813581610000000057190121704385700000001613054714739080000000428786696323048000000
30107196674080762000000251448988584503000005519611944537880000113052292839932000021533770064749000038000770702498300618617197482530092437052497389612605052613280415579278510796
3117310309456441712008627561488703154411205292567152314437643761568494851991
32
Sheet7
Cell Formulas
RangeFormula
B3,B21,B18,B6B3=FORMULATEXT(B4)
B4:CW4B4=LPSC(99)
B7:K16,B22:K31B7=WRAPROWS(B4#,10)
B19:CW19B19=CPSC(99)
Dynamic array formulas.
 
Last edited:
Reverse engineering of COMBIN's concept to calculate large combin nr. => LCOMBIN(n,c,[l]) calls LPSC(n )
LCOMBIN(n,c,[l])
Excel Formula: 
=LAMBDA(n,c,[l],LET(o,COMBIN(n,c),e,ISERR(o),IF(e,o,IF(l,INDEX(LPSC(n),1,c+1),o))))
n: nr. items
c: nr. chosen
[l]: large arg. if 1 triggers LPSC calc, if omitted regular COMBIN calculated
Book1.xlsx
ABCDEFGHIJKLMNOP
1
2COMBIN concept of a Pascal's Triangle line
3n=13
4=CPSC(13)
511378286715128717161716128771528678131
6
7vector size => n+1
8=COLUMNS(B5#)
914
10
11=SEQUENCE(,B9)-1
12012345678910111213
13
14combin(n,c) <=> Pascal's triangle line
15=COMBIN(13,B12#)
1611378286715128717161716128771528678131
17
18Note: Symmetrical distribution smallest to largest at the center. max=combin(n,n/2)
19
20Conclusion:
21If we calculate PSC using precise LPSC method every value correspond to a combin(n,c) => precise combin calculation
22
Sheet9
Cell Formulas
RangeFormula
B4,B15,B11,B8B4=FORMULATEXT(B5)
B5:O5B5=CPSC(13)
B9B9=COLUMNS(B5#)
B12:O12B12=SEQUENCE(,B9)-1
B16:O16B16=COMBIN(13,B12#)
Dynamic array formulas.

Book1.xlsx
ABCDEFGH
1Checking
2LCOMBIN/COMBIN behavior ,( if c<0 or c>n => #NUM error)LargeCOMBIN "precise" calculation
3
4c<0c<0n,200,c,100,l,1 => precise large combin
5=LCOMBIN(5,-1,1)=COMBIN(5,-1)=LCOMBIN(200,100,1)
6#NUM!#NUM!90548514656103281165404177077484163874504589675413336841320
7
8c>nc>nn,200,c,100,l,omitted => regular COMBIN calc
9=LCOMBIN(5,6,1)=COMBIN(5,6)=LCOMBIN(200,100)
10#NUM!#NUM!90548514656103200000000000000000000000000000000000000000000
11(format cell Number)
12c=0c=0
13=LCOMBIN(5,0,1)=COMBIN(5,0)regular COMBIN (lot of trailing 0's
1411=COMBIN(200,100)
159.05485E+58
16c=nc=n(format cell General)
17=LCOMBIN(5,5,1)=COMBIN(5,5)
1811
19
Sheet10
Cell Formulas
RangeFormula
B5,D5,B17,D17,G14,B13,D13,G9,B9,D9,G5B5=FORMULATEXT(B6)
B6B6=LCOMBIN(5,-1,1)
D6D6=COMBIN(5,-1)
G6G6=LCOMBIN(200,100,1)
B10B10=LCOMBIN(5,6,1)
D10D10=COMBIN(5,6)
G10G10=LCOMBIN(200,100)
B14B14=LCOMBIN(5,0,1)
D14D14=COMBIN(5,0)
G15G15=COMBIN(200,100)
B18B18=LCOMBIN(5,5,1)
D18D18=COMBIN(5,5)
 
Xlambda,

This is absolute algebraic magic : ) : ) : ) You are a math wiz for initially not knowing anything about Pascal's Triangle, and then producing multiple functions about that topic and related topics. Thank you for your help. I will send the link to this r Excel Board post to the guy who asked me the question.

You are THE best : )

Sincerely, Mike "Thanks For Pascal Help" Girvin
 
After asking Mike Girvin some advice about Pascal’s Triangle as an educational exercise in understanding lambda recursion, he pointed me to this forum. I’ve only been able to glance it over for now and see that the double recursion I meanwhile found myself is along the lines of what you’re proposing, although it looks like your individual lambda’s are sleeker than mine. And there seems to be so much exciting stuff on this page alone, I think I found a new favourite reading place … thanks for all of your contributions !!
 
mgirvin said:
Xlambda,

This is absolute algebraic magic : ) : ) : ) You are a math wiz for initially not knowing anything about Pascal's Triangle, and then producing multiple functions about that topic and related topics. Thank you for your help. I will send the link to this r Excel Board post to the guy who asked me the question.

You are THE best : )

Sincerely, Mike "Thanks For Pascal Help" Girvin
Click to expand...
Thanks, Mike, for bringing Mr. Excel into my life ;)
 
piProductivITy,

Mr Excel Message Board AND Xlambda (Excel Lambda at YouTube). He brings such elegant and efficient solutions to us all : ) : ) : )
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,842
Messages
6,127,227
Members
449,371
Latest member
strawberrish

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top