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
837
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
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.xlsx
ABCDEFGHIJKL
1
2Basic recursive lambda that reverses any string. (1 variable, "a")
3
4defined REV(a)=LAMBDA(a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&REV(LEFT(a,n-1)))))
5check
6=REV(B7)=TEXTJOIN(,,MID(B7,SEQUENCE(,LEN(B7),LEN(B7),-1),1))
7aBcD,:123321:,DcBa321:,DcBa
8
9to make it call itself in a cell, not defined
10
11=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:,DcBa
13
14
15Recursive lambda that adds all digits. (2 variables, "a" and "s")
16
17defined 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))))
18check
19=ADDIG(B20)=SUM(--MID(B20,SEQUENCE(LEN(B20)),1))
20text→328512093030
21
22=ADDIG(B23)
23number→3285120930
24
25in 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))
2630
27
in cell recursive 1
Cell Formulas
RangeFormula
G6,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))
 
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLMNOPQR
1
2Recursive lambda that extracts only the digits. (2 variables, double self calling)
3
4defined 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)))))
5
6=EXDIG(B7)
71g23T(45)Pz%6123456
8
9in 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))
10123456
11
12
13Recursive lambda that extracts and adds digits
14
15defined 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 lambdas
17=ADDEXDIG(B18)=ADDIG(EXDIG(B7))
181g23T(45)Pz%62121
19
20in 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))
2121
22
23
24As 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 LET
25
26RL(a,b)=LAMBDA(a,b,LET(…..a1….a2…IF(exit cond.,acumulator,RL(a1,b1))))
27
28 =LET(a,cell,fn,LAMBDA(y,a,b,LET(….a1…a2….IF(exit cond.,acumulator,y(y,a1,b1)))),fn(fn,a,b))
29
30 - 1st part input variable, 2nd part lambda variable, 3rd part calling lambda variable
31
in cell recursive 2
Cell Formulas
RangeFormula
D6,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))
 
All recursive functions:

REV(a) Reverse any string
a: single value/cell reference, any string
Excel Formula: 
=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.
Excel Formula: 
=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
Excel Formula: 
=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
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNO
1
2extracting digitsadding digitsextracting and adding digits
3=MAP(B4:B6,LAMBDA(x,EXDIG(x)))=MAP(D4#,LAMBDA(x,ADDIG(x)))=MAP(B4:B6,LAMBDA(x,ADDEXDIG(x)))
4e7R%-;6gH95>28j7695283737
590876xRTy2@#57908762574444
6b9999K9888888x77799999888888777114114
7
8reversed
9=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;-%R7e8259673737
1175#@2yTRx67809752678094444
12777x8888889K9999b77788888899999114114
13
14sum of digits in reversed order deliver same results
15
16Note:
17EXDIG keeps the result in text format for not allowing Excel to turn it into scientific format with E when many digits are extracted.
18
19=--D6
209.99999E+13
21
in cell recursive 3
Cell Formulas
RangeFormula
D3,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=--D6
Dynamic array formulas.
 
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.xlsx
ABCDEFGHIJKLM
1License Plate Challenge
24th solution, in cell recursive lambda
3=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"))
6123M57OddOdd
71COU886EvenEvencheck
81D48821OddOdd=AND(tblChallenge[Odd/Even]=E6#)
91DVT189OddOddTRUE
1021W7454EvenEven
1127U17OddOdd
122IGR878EvenEven
132RDZ434EvenEven
142SWQ185OddOdd
153FII697OddOdd
163QOW866EvenEven
173RQC229OddOdd
183RSZ114EvenEven
194FXL296EvenEven
204KUC981OddOdd
214QKB212EvenEven
224QRG241OddOdd
234RVF139OddOdd
245ACY568EvenEven
255IYF234EvenEven
265KFC128EvenEven
275NFD419OddOdd
285RXA984EvenEven
295TGB871OddOdd
305UEZ777OddOdd
315WKT763OddOdd
326LJT722EvenEven
336MVB117OddOdd
346XZR265OddOdd
3571F27UJOddOdd
36745Y4IEvenEven
3776736QEvenEven
387FOB933OddOdd
397JCM371OddOdd
40884PCXEvenEven
418JHC777OddOdd
428RDQ178EvenEven
438TMW513OddOdd
449PMH329OddOdd
459UGW414EvenEven
469UUL349OddOdd
47BEACHOddOdd
48G97C8TEvenEven
49H1R226IEvenEven
50KL7469COddOdd
51RUSSOddOdd
52S94COEvenEven
53T5A5MENOddOdd
54YELOSTNOddOdd
55YOSEMITOddOdd
56
Challenge
Cell Formulas
RangeFormula
B3B3=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.
 
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
Excel Formula: 
=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))))
Book1
ABCDEFGHIJKLMNOPQRST
1
2limit2. reduce, array of accumulators
32000=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. classic
5↓↓↓=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↓↓↓↓↓↓checking
7=RTL(C8:C38)↓↓↓↓↓↓=SCAN(0,C8:C37,LAMBDA(v,i,v+i))
8198202107202107202107198
921552253
103753328
1141784506
125315537
13626539
1471967735
158568791
1691289919
1710104101023
181160111083
1912182121265
201311131276
2114182141458
221594151552
2316153161705
241794171799
251880181879
261996191975
2720132202107
2821200212307
2922155222462
302348232510
312454242564
3225140252704
33269262713
3427117272830
352897282927
3629168293095
373028303123
38
Sheet1
Cell Formulas
RangeFormula
H3H3=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.
 
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.
 
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 )
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2=KPRKR(1495)startCF values <> 6174
361741000
4
5=KPRKR(1482)=SEQUENCE(10,20,1000)
6617410001001100210031004100510061007100810091010101110121013101410151016101710181019
710201021102210231024102510261027102810291030103110321033103410351036103710381039
8=KPRKR(8273)10401041104210431044104510461047104810491050105110521053105410551056105710581059
9617410601061106210631064106510661067106810691070107110721073107410751076107710781079
1010801081108210831084108510861087108810891090109110921093109410951096109710981099
1111001101110211031104110511061107110811091110111111121113111411151116111711181119
1211201121112211231124112511261127112811291130113111321133113411351136113711381139
1311401141114211431144114511461147114811491150115111521153115411551156115711581159
1411601161116211631164116511661167116811691170117111721173117411751176117711781179
1511801181118211831184118511861187118811891190119111921193119411951196119711981199
16
17=MAP(E6#,KPRKR)
1806174617461746174617461746174617461746174061746174617461746174617461746174
1961746174617461746174617461746174617461746174617461746174617461746174617461746174
2061746174617461746174617461746174617461746174617461746174617461746174617461746174
2161746174617461746174617461746174617461746174617461746174617461746174617461746174
2261746174617461746174617461746174617461746174617461746174617461746174617461746174
2361740617461746174617461746174617461740006174617461746174617461746174
2461740617461746174617461746174617461746174617461746174617461746174617461746174
2561746174617461746174617461746174617461746174617461746174617461746174617461746174
2661746174617461746174617461746174617461746174617461746174617461746174617461746174
2761746174617461746174617461746174617461746174617461746174617461746174617461746174
28
29
K 1
Cell Formulas
RangeFormula
B2,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 Formatting
CellConditionCell FormatStop If True
E18:X27Expression=E18=0textNO
E6:X15Expression=E18=0textNO
 
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 )
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2startCF values <> 6174
31000
4
5=SEQUENCE(10,20,B3)
610001001100210031004100510061007100810091010101110121013101410151016101710181019
710201021102210231024102510261027102810291030103110321033103410351036103710381039
810401041104210431044104510461047104810491050105110521053105410551056105710581059
910601061106210631064106510661067106810691070107110721073107410751076107710781079
1010801081108210831084108510861087108810891090109110921093109410951096109710981099
1111001101110211031104110511061107110811091110111111121113111411151116111711181119
1211201121112211231124112511261127112811291130113111321133113411351136113711381139
1311401141114211431144114511461147114811491150115111521153115411551156115711581159
1411601161116211631164116511661167116811691170117111721173117411751176117711781179
1511801181118211831184118511861187118811891190119111921193119411951196119711981199
16
17IMP 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)
1961746174617461746174617461746174617461746174617461746174617461746174617461746174
2061746174617461746174617461746174617461746174617461746174617461746174617461746174
2161746174617461746174617461746174617461746174617461746174617461746174617461746174
2261746174617461746174617461746174617461746174617461746174617461746174617461746174
2361746174617461746174617461746174617461746174617461746174617461746174617461746174
2461746174617461746174617461746174617461746174061746174617461746174617461746174
2561746174617461746174617461746174617461746174617461746174617461746174617461746174
2661746174617461746174617461746174617461746174617461746174617461746174617461746174
2761746174617461746174617461746174617461746174617461746174617461746174617461746174
2861746174617461746174617461746174617461746174617461746174617461746174617461746174
29
K 2
Cell Formulas
RangeFormula
B5,B18B5=FORMULATEXT(B6)
B6:U15B6=SEQUENCE(10,20,B3)
B19:U28B19=MAP(B6#,KPK)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B19:U28Expression=B19=0textNO
B6:U15Expression=B19=0textNO
 
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.xlsx
ABCDEFGHIJKLMN
1Checking all values btwn 1000 and 9999 (all 9000 values)
2all values = 0all values = 6174
3=SEQUENCE(9000,,1000)=MAP(B4#,LAMBDA(x,KPK(x)))=FILTER(B4#,D4#=0)=FILTER(B4#,D4#=6174)
41000617411111000
51001617422221001=ROWS(F4#)+ROWS(H4#)
610026174333310029000
71003617444441003 =>
81004617455551004all values are 6174, except the expected 9 0's values
91005617466661005
101006617477771006
111007617488881007
121008617499991008
13100961741009
14101061741010
15101161741011
16101261741012
17101361741013
18101461741014
19101561741015
20101661741016
21101761741017
22101861741018
23101961741019
24102061741020
25102161741021
26102261741022
27102361741023
28102461741024
29102561741025
30102661741026
31102761741027
32102861741028
33102961741029
34103061741030
35103161741031
36103261741032
37103361741033
38103461741034
39103561741035
40103661741036
41103761741037
42103861741038
43103961741039
44104061741040
45104161741041
46104261741042
47104361741043
48104461741044
49104561741045
50104661741046
51104761741047
52104861741048
53104961741049
54105061741050
55105161741051
56105261741052
57105361741053
58105461741054
59105561741055
60105661741056
61105761741057
62105861741058
63105961741059
64106061741060
65106161741061
K 3
Cell Formulas
RangeFormula
B3,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.
 
Mike (ExcelIsFun) asked my opinion about solving a super cool problem using recursion, The Pascal's triangle.
Here are my solutions.
Concept: Tool lambda. Calculating a line from previous line, Pascal's triangle Line
PL(x) where x: previous line (row vector)
Excel Formula: 
=LAMBDA(x,HSTACK(1,DROP(x,,-1)+DROP(x,,1),1))
Recursive 1: Recursive Pascal's triangle n'th row, calls PL
RPSC(n ) n: N>=3
Excel Formula: 
=LAMBDA(n,[p],LET(x,IF(ISOMITTED(p),{1,1},p),IF(COLUMNS(x)=n,p,RPSC(n,PL(x)))))
Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1Concept: Tool function PL(x) Pascal's triangle Line
2
3nPascal's triangle (left aligned)
411
5211
63121
741331
8514641
9615101051
1071615201561
118172135352171
12918285670562881
1310193684126126843691
14111104512021025221012045101
15121115516533046246233016555111
16131126622049579292479249522066121
171411378286715128717161716128771528678131
181511491364100120023003343230032002100136491141
191611510545513653003500564356435500530031365455105151
2017116120560182043688008114401287011440800843681820560120161
21181171366802380618812376194482431024310194481237661882380680136171
2219118153816306085681856431824437584862043758318241856485683060816153181
23201191719693876116282713250388755829237892378755825038827132116283876969171191
24
25Concept: How to calculate line 8 if we have previous line, line 7
26=DROP(D27:J27,,-1)
27line71615201561step1161520156
28=DROP(D27:J27,,1)
29step2615201561
30=N27#+N29#
31step37213535217
32=HSTACK(1,N31#,1)
33line8172135352171step4172135352171
34
35=PL(D27:J27)
36172135352171
37
38=PL(D33:K33)
3918285670562881
40
Sheet1
Cell Formulas
RangeFormula
N26,D38,D35,N32,N30,N28N26=FORMULATEXT(N27)
N27:S27N27=DROP(D27:J27,,-1)
N29:S29N29=DROP(D27:J27,,1)
N31:S31N31=N27#+N29#
N33:U33N33=HSTACK(1,N31#,1)
D36:K36D36=PL(D27:J27)
D39:L39D39=PL(D33:K33)
Dynamic array formulas.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3=RPSC(3)
4121
5
6=RPSC(5)
714641
8
9=RPSC(10)
10193684126126843691
11
12=RPSC(20)
131191719693876116282713250388755829237892378755825038827132116283876969171191
14
15=RPSC(25)
1612427620241062642504134596346104735471130750419612562496144270415624961441961256130750473547134610413459642504106262024276241
17
Sheet2
Cell Formulas
RangeFormula
B3,B15,B12,B9,B6B3=FORMULATEXT(B4)
B4:D4B4=RPSC(3)
B7:F7B7=RPSC(5)
B10:K10B10=RPSC(10)
B13:U13B13=RPSC(20)
B16:Z16B16=RPSC(25)
Dynamic array formulas.


Recursive 2: Recursive Pascal's entire Triangle calls PL
RPSCT(n ) n: N>=3
Excel Formula: 
=LAMBDA(n,[p],LET(x,IF(ISOMITTED(p),{1,"";1,1},p),y,TAKE(x,-1),c,COLUMNS(y),IF(c=n,IFNA(p,""),RPSCT(n,VSTACK(x,PL(y))))))
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2=RPSCT(3)
31
411
5121
6
7=RPSCT(30)
81
911
10121
111331
1214641
1315101051
141615201561
15172135352171
1618285670562881
17193684126126843691
181104512021025221012045101
191115516533046246233016555111
201126622049579292479249522066121
2111378286715128717161716128771528678131
2211491364100120023003343230032002100136491141
2311510545513653003500564356435500530031365455105151
24116120560182043688008114401287011440800843681820560120161
251171366802380618812376194482431024310194481237661882380680136171
26118153816306085681856431824437584862043758318241856485683060816153181
271191719693876116282713250388755829237892378755825038827132116283876969171191
281201901140484515504387607752012597016796018475616796012597077520387601550448451140190201
29121210133059852034954264116280203490293930352716352716293930203490116280542642034959851330210211
30122231154073152633474613170544319770497420646646705432646646497420319770170544746132633473151540231221
31123253177188553364910094724515749031481719011440661352078135207811440668171904903142451571009473364988551771253231
3212427620241062642504134596346104735471130750419612562496144270415624961441961256130750473547134610413459642504106262024276241
3312530023001265053130177100480700108157520429753268760445740052003005200300445740032687602042975108157548070017710053130126502300300251
341263252600149506578023023065780015622753124550531173577261609657700104006009657700772616053117353124550156227565780023023065780149502600325261
351273512925175508073029601088803022200754686825843628513037895173838602005830020058300173838601303789584362854686825222007588803029601080730175502925351271
361283783276204759828037674011840403108105690690013123110214741803042175537442160401166003744216030421755214741801312311069069003108105118404037674098280204753276378281
371294063654237511187554750201560780429214510015005200300103459729051895935678639157755876077558760678639155189593534597290200300101001500542921451560780475020118755237513654406291
38
Sheet3
Cell Formulas
RangeFormula
B2,B7B2=FORMULATEXT(B3)
B3:D5B3=RPSCT(3)
B8:AE37B8=RPSCT(30)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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