AXMAS

=AXMAS(ht,sh,or)
ht
tree's height (nr. of cells)
sh
step or stair's height
or
ornaments array
Happy Holidays everyone!! For the ones who celebrate Xmas, 2 Xmas tree designs.
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
AXMAS , AX two Xmas's trees design.
AX is a "tree" that is already decorated, does not call any other lambda. The ornaments are embedded in the function.
AXMAS is an undecorated one. We can choose our own ornaments array "or". Call AFLAT.
AFLAT , flattens the ornament array, and gives us the versatility to deal with arrays of any shape, size, dimensions, with blanks/null strings, ranges, or other array formulas.
Both designs, randomly, change the display of ornaments every time spreadsheet recalculates, or if we hit F9.
Both lambdas share same concept design, creating a Xmas tree shape, a "triangular" array that can be filled with random values.
A triangular array is like designing a symmetric staircase, it has stairs or steps, and has 2 input parameters "ht", the approximate total height, and "sh", each stair height. Each stair width or depth is by default 1. Knowing these parameters, the function knows how to calculate nr. of steps, exact total height to accommodate nr. of steps and total width footprint of the "staircase".
Both lambdas share same ornament topper (2 parts), and same tree base, (last row) where presents boxes are placed. First variables after LET.
AXMAS(ht,sh,or)
Excel Formula: 
=LAMBDA(ht,sh,or,
     LET(t,UNICHAR(11088),i,UNICHAR(9618),p,UNICHAR(127873),
        o,AFLAT(or),n,ROWS(o),h,MAX(9,ht),s,MAX(3,sh),ns,ROUNDUP(h/s,0),y,ns*s+1,x,2*ns-1,r,SEQUENCE(y),c,ABS(SEQUENCE(,x)-ns),
        a,QUOTIENT(r-1,s)>=c,b,INDEX(o,RANDARRAY(y,x,1,n,1)*a),w,SWITCH(r,1,t,2,i,y,IF(c<ns-2,p,""),b),
       IF(a,w,"")
    )
)
AX(ht,sh)
Excel Formula: 
=LAMBDA(ht,sh,
    LET(t,UNICHAR(11088),i,UNICHAR(9618),p,UNICHAR(127873),q,SEQUENCE(44,,10035),o,UNICHAR(IF(q>10058,q+117698,q)),
        n,44,h,MAX(9,ht),s,MAX(3,sh),ns,ROUNDUP(h/s,0),y,ns*s+1,x,2*ns-1,r,SEQUENCE(y),c,ABS(SEQUENCE(,x)-ns),
        a,QUOTIENT(r-1,s)>=c,b,INDEX(o,RANDARRAY(y,x,1,n,1)*a),w,SWITCH(r,1,t,2,i,y,IF(c<ns-2,p,""),b),
        IF(a,w,"")
    )
)
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1AXMAS(ht,sh,or)ornaments array sample
2ht: approx.. height; if ht ignored or <9 => ht=9, (min tree height=9)❀❁❂❃❅❆??✿✽✾
3sh: each "step" height; if ignored or <3 =>sh=3 (3 min."stair" height=3 )smallest treeht,15,sh,4
4or: ornaments array, any array, any shape, any valuesht,,sh,,(ignored)=AXMAS(15,4,X2:AH2)
5Increasing sh value makes triangle's top angle smaller, (higher tree "slope")=AXMAS(,,X2:AH2) ⭐
6For better visual experience, change the grids width to tiny squares, all centered aligned. ⭐▒
7Predetermined ornaments, topper, 2 parts, tree base,present boxes.▒✿
8If you want to change them, just after LET, variables t,i,p?❁
9❅❃❀✽❅✽
1011088⭐topper 1❃❆????
119618▒topper 2❃❆❅✽✽❀
12127873?base✽?❂✿❂❅❀❅
13❂✽❅✾❁✾❀❆✿✾
14Ornaments array values used by these trees in next post.?✾✾❀❀❆❁❆❆✿
15Note: Ornaments shape, color, can look different on your spreadsheet.??❅❂❀❃
16✿?❂❂❃
17ht,30,sh,4❀✾❀?❁❅❅
18=AXMAS(30,4,BH17:CU20)❃?❃❃❃✿✽
19 ⭐ht,20,sh,, ❆✾?❃✿❀❃
20▒=AXMAS(20,,BH17:CU20)❂✽✽❂❅❃✿
21⏬ ⭐???
22?▒
23????
24?????♕ornaments array as numbers
25⏬???✼?=AXMAS(13,4,SEQUENCE(9))
26?????? ⭐
27???❀??????▒
28✷?????????5
29?????⚽????3
30❊❇?❉?????❄??641
31✻???✅????⏪?⭕??652
32???⭕?⚓???✺???✴397
33????❌??????❎??❄?254
34?⏩✼?⭕✴????❈?????46944
35?????❇????⭕✻?❆?✻??53637
36✺⏩❂?⛄??✺??????????⚡⚡53239
37?????⚓???❈????✿?✾?❂?28228
38???✸???✿?????❁???❁??2773484
39?❉?????✿?♕❉??❎??✶???⏩??❀1397695
40???????????✻????❂???❉??❂6196182
41?✼??❅❈?❃✅✅?❆✹⚓??⭐⏬❊???⚡?6214983
42???????????????????????
43????✅???⛄????
44???❄??❄?✼⏬???Note: This values can be used with CF
45???????????♔❅
46??❊????????❀?
47??????????????⚡
48??⚽??????✾?❊???
49??✵✵???❊?❃?????
50?✶??✻?????❈????
51???????????
52
AXMAS 1
Cell Formulas
RangeFormula
AG4,AI25,T20,B18,Y5AG4=FORMULATEXT(AG5)
AG5:AM21AG5=AXMAS(15,4,X2:AH2)
Y6:AC15Y6=AXMAS(,,X2:AH2)
B10:B12B10=UNICHAR(A10:A12)
B19:P51B19=AXMAS(30,4,BH17:CU20)
T21:AF42T21=AXMAS(20,,BH17:CU20)
AI26:AO42AI26=AXMAS(13,4,SEQUENCE(9))
Dynamic array formulas.
 
Upvote 0
Having fun, again. Deck of cards, 13 ranks, 4 suits. Concept
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1browsing for suits unicodesorder by suitsclmn with CForder by ranksclmn with CF
2=AUNICHAR(9812,20,1,1)=SEQUENCE(52)↓=SEQUENCE(52)↓
3♔ 9812↓=QUOTIENT(G8#-1,4)+2↓=MOD(P8#-1,13)+2
4♕ 98139824♠spades↓↓=MOD(G8#-1,4)↓↓=QUOTIENT(P8#-1,13)
5♖ 98149827♣clubs↓↓↓=SWITCH(H8#,11,"A",12,"J",13,"Q",14,"K",H8#)↓↓↓=SWITCH(Q8#,11,"A",12,"J",13,"Q",14,"K",Q8#)
6♗ 98159829♥hearts↓↓↓↓=SWITCH(I8#,0,D4,1,D5,2,D6,3,D7)↓↓↓↓=SWITCH(R8#,0,D4,1,D5,2,D6,3,D7)
7♘ 98169830♦diamonds↓↓↓↓↓=J8#&K8#↓↓↓↓↓=S8#&T8#
8♙ 98171202♠2♠1202♠2♠
9♚ 98182212♣2♣2303♠3♠
10♛ 98199825♡hearts3222♥2♥3404♠4♠
11♜ 98209826♢diamonds4232♦2♦4505♠5♠
12♝ 98219828♤spades5303♠3♠5606♠6♠
13♞ 98229831♧clubs6313♣3♣6707♠7♠
14♟ 98237323♥3♥7808♠8♠
15♠ 9824other function8333♦3♦8909♠9♠
16♡ 9825AUNICHAR9404♠4♠910010♠10♠
17♢ 982610414♣4♣10110A♠A♠
18♣ 982711424♥4♥11120J♠J♠
19♤ 982812434♦4♦12130Q♠Q♠
20♥ 982913505♠5♠13140K♠K♠
21♦ 983014515♣5♣14212♣2♣
22♧ 983115525♥5♥15313♣3♣
2316535♦5♦16414♣4♣
2417606♠6♠17515♣5♣
2518616♣6♣18616♣6♣
2619626♥6♥19717♣7♣
2720636♦6♦20818♣8♣
2821707♠7♠21919♣9♣
2922717♣7♣2210110♣10♣
3023727♥7♥23111A♣A♣
3124737♦7♦24121J♣J♣
3225808♠8♠25131Q♣Q♣
3326818♣8♣26141K♣K♣
3427828♥8♥27222♥2♥
3528838♦8♦28323♥3♥
3629909♠9♠29424♥4♥
3730919♣9♣30525♥5♥
3831929♥9♥31626♥6♥
3932939♦9♦32727♥7♥
403310010♠10♠33828♥8♥
413410110♣10♣34929♥9♥
423510210♥10♥3510210♥10♥
433610310♦10♦36112A♥A♥
4437110A♠A♠37122J♥J♥
4538111A♣A♣38132Q♥Q♥
4639112A♥A♥39142K♥K♥
4740113A♦A♦40232♦2♦
4841120J♠J♠41333♦3♦
4942121J♣J♣42434♦4♦
5043122J♥J♥43535♦5♦
5144123J♦J♦44636♦6♦
5245130Q♠Q♠45737♦7♦
5346131Q♣Q♣46838♦8♦
5447132Q♥Q♥47939♦9♦
5548133Q♦Q♦4810310♦10♦
5649140K♠K♠49113A♦A♦
5750141K♣K♣50123J♦J♦
5851142K♥K♥51133Q♦Q♦
5952143K♦K♦52143K♦K♦
60
61CF for L8:L59
62 =OR(UNICODE(RIGHT(L8,1))=9829,UNICODE(RIGHT(L8,1))=9830)
63
ADECK 1
Cell Formulas
RangeFormula
A2,U7,L7A2=FORMULATEXT(A3)
G2,P2G2=FORMULATEXT(G8)
A3:A22A3=AUNICHAR(9812,20,1,1)
H3,Q3H3=FORMULATEXT(H8)
D4:D7,D10:D13D4=UNICHAR(C4:C7)
I4,R4I4=FORMULATEXT(I8)
J5,S5J5=FORMULATEXT(J8)
K6,T6K6=FORMULATEXT(K8)
G8:G59,P8:P59G8=SEQUENCE(52)
H8:H59H8=QUOTIENT(G8#-1,4)+2
I8:I59I8=MOD(G8#-1,4)
J8:J59,S8:S59J8=SWITCH(H8#,11,"A",12,"J",13,"Q",14,"K",H8#)
K8:K59K8=SWITCH(I8#,0,D4,1,D5,2,D6,3,D7)
L8:L59,U8:U59L8=J8#&K8#
Q8:Q59Q8=MOD(P8#-1,13)+2
R8:R59R8=QUOTIENT(P8#-1,13)
T8:T59T8=SWITCH(R8#,0,D4,1,D5,2,D6,3,D7)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U8:U59Expression=OR(UNICODE(RIGHT(U8,1))=9829,UNICODE(RIGHT(U8,1))=9830)textNO
L8:L59Expression=OR(UNICODE(RIGHT(L8,1))=9829,UNICODE(RIGHT(L8,1))=9830)textNO
 
ADECK([sh]). The function.
[sh]: shuffling argument: 0 or omitted deck shuffled, 1, cards ordered by suits.
First variables after LET, s,c,h,d unicode values for spades, clubs, hearts, diamonds, if we want to change them.
Note: I could have chosen to make ADECK argument free, and always shuffle it, but I've considered that also having a deck in order could be useful. Anyhow ADECK() looks like an argument free function, but it isn't, has a single argument that can be omitted.
Excel Formula: 
=LAMBDA([sh],
    LET(s,9824,c,9827,h,9829,d,9830,sq,SEQUENCE(52),q,QUOTIENT(sq-1,4)+2,m,MOD(sq-1,4),
       k,SWITCH(q,11,"A",12,"J",13,"Q",14,"K",q)&UNICHAR(SWITCH(m,0,s,1,c,2,h,3,d)),
       IF(sh,k,SORTBY(k,RANDARRAY(52)))
    )
)
AXMAS.xlsx
ABCDEFGHIJKLMN
1ADECK([sh]) function. A shuffled deck needs to have all the cards, and no duplicates
2
3Shuffling a single deckcheck
4=RANDARRAY(52)=SORTBY(A5:A56,B5#)=ADECK(1)=ADECK()=ADECK()=ROWS(UNIQUE(J5#))
52♠0.15922610♣2♠2♦10♠52
62♣0.341684♠2♣J♣A♣
72♥0.125188J♠2♥J♠J♣
82♦0.9404582♥2♦2♥5♠
93♠0.6931822♠3♠A♥8♠
103♣0.4525344♥3♣8♣J♦
113♥0.2081919♠3♥8♦J♠
123♦0.6427789♣3♦5♣9♦
134♠0.0269043♥4♠Q♣3♥
144♣0.9741189♦4♣9♥3♣
154♥0.1635164♦4♥2♠8♣
164♦0.2971466♣4♦4♦7♣
175♠0.390451A♥5♠10♦9♠
185♣0.8353325♥5♣Q♦Q♣
195♥0.339222♣5♥A♣Q♠
205♦0.8098568♥5♦K♣Q♦
216♠0.846066K♠6♠3♦10♥
226♣0.309345♠6♣Q♥4♦
236♥0.445142A♠6♥7♦A♥
246♦0.4023466♦6♦A♠A♦
257♠0.736334K♥7♠Q♠3♦
267♣0.905782J♥7♣3♣9♣
277♥0.4226757♥7♥6♥J♥
287♦0.6239046♥7♦6♦4♠
298♠0.6529653♣8♠K♠7♦
308♣0.9625369♥8♣9♠9♥
318♥0.380866J♣8♥5♠6♠
328♦0.64167210♦8♦4♥2♣
339♠0.187997♦9♠J♥3♠
349♣0.2059878♦9♣7♣10♦
359♥0.5079993♦9♥10♥K♥
369♦0.234728♠9♦K♦Q♥
3710♠0.824449A♦10♠4♠8♦
3810♣0.0039123♠10♣8♠5♦
3910♥0.927853A♣10♥2♣10♣
4010♦0.600534Q♦10♦3♥7♥
41A♠0.3985497♠A♠5♥K♦
42A♣0.713083Q♣A♣9♣6♥
43A♥0.330515Q♥A♥8♥K♣
44A♦0.662569J♦A♦6♠2♥
45J♠0.03395♦J♠10♣6♣
46J♣0.52591310♠J♣4♣A♠
47J♥0.4146265♣J♥K♥5♥
48J♦0.7832176♠J♦6♣2♦
49Q♠0.9952497♣Q♠7♠5♣
50Q♣0.744687K♦Q♣3♠4♣
51Q♥0.75253910♥Q♥5♦K♠
52Q♦0.73581K♣Q♦J♦8♥
53K♠0.3883672♦K♠A♦4♥
54K♣0.9324068♣K♣7♥6♦
55K♥0.404414♣K♥9♦7♠
56K♦0.91445Q♠K♦10♠2♠
57
ADECK 2
Cell Formulas
RangeFormula
B4,D4,F4,H4,J4,L4B4=FORMULATEXT(B5)
B5:B56B5=RANDARRAY(52)
D5:D56D5=SORTBY(A5:A56,B5#)
F5:F56F5=ADECK(1)
H5:H56,J5:J56H5=ADECK()
L5L5=ROWS(UNIQUE(J5#))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D5:P56Expression=OR(UNICODE(RIGHT(D5,1))=9829,UNICODE(RIGHT(D5,1))=9830)textNO
A5:A56Expression=OR(UNICODE(RIGHT(A5,1))=9829,UNICODE(RIGHT(A5,1))=9830)textNO
 
Shuffling n separate decks of cards. It could be a very useful tool for memory athletes.
Alex Mullen is an American memory competitor, three-time world memory champion, and physician.
Alex Mullen memorized the order of over 28 decks of cards at the 2015 World Memory Championships in Chengdu, China.
AXMAS.xlsx
ABCDEFGHIJKLMNO
1Part 1. Static method. Shuffle n deck of cards, separate decks, each deck a separate column, easy to visualize, like in a memory contest, see picture.
2
3Shuffling 3 decks of cards with IFShuffling 3 decks of cards with CHOOSEShuffling 3 decks of cards with SWITCH
4not good, for every iterationok, ADECK() calculates 3 times, butok, but, like CHOOSE we have to write
5ADECK() should recalculatetoo static, imagine writing ADECK() 28timesexplicitly all iterations
6=IF(SEQUENCE(,3),ADECK())=CHOOSE({1,2,3},ADECK(),ADECK(),ADECK())=SWITCH(SEQUENCE(,3),1,ADECK(),2,ADECK(),3,ADECK())
77♣7♣7♣5♣8♦4♣J♣8♣J♦
8Q♣Q♣Q♣Q♠3♦6♥7♥J♥A♠
910♣10♣10♣8♥10♣4♠K♦7♥8♥
10A♣A♣A♣Q♣A♥J♥9♥J♠2♦
116♣6♣6♣6♣6♦A♦6♦7♣5♠
125♥5♥5♥6♠7♠K♦5♠6♠4♦
1310♥10♥10♥6♦Q♣Q♦10♣3♣3♣
145♦5♦5♦K♣K♠8♣10♥3♦10♦
157♦7♦7♦J♣5♠3♣10♠A♦3♥
168♣8♣8♣3♦4♠3♥3♣Q♦K♠
179♣9♣9♣K♠9♦8♠6♣2♠7♣
18Q♥Q♥Q♥5♠Q♠Q♣2♠6♦K♥
192♦2♦2♦2♥A♦9♣6♥J♣5♣
204♦4♦4♦10♠10♠10♠4♠J♦6♣
214♣4♣4♣3♥10♦5♥Q♣5♥6♥
22Q♦Q♦Q♦3♠6♠7♣Q♠9♣10♥
238♠8♠8♠J♥J♥9♠A♥10♠5♦
2410♦10♦10♦7♣7♥6♦2♣4♥Q♦
257♠7♠7♠10♦7♦K♣7♣2♥J♠
262♠2♠2♠A♦K♥7♥3♠8♦9♦
27A♠A♠A♠5♥2♣A♠7♠K♣A♦
283♦3♦3♦9♣A♠5♠6♠K♥3♦
29K♠K♠K♠9♥3♥7♦5♦5♦A♥
304♠4♠4♠4♦2♥4♦9♣10♦Q♣
3110♠10♠10♠9♠10♥3♠3♥K♦9♣
329♥9♥9♥J♠9♥10♦8♣8♠3♠
339♦9♦9♦A♥J♦J♣5♥8♥4♥
346♥6♥6♥10♥8♣Q♠4♣K♠10♠
356♦6♦6♦8♦Q♥10♥K♣Q♥6♠
36K♥K♥K♥Q♦Q♦7♠8♦4♣K♣
37A♦A♦A♦4♥2♦2♣J♠10♥2♥
38Q♠Q♠Q♠5♦9♣3♦7♦Q♠7♠
39J♦J♦J♦8♣7♣5♣Q♥A♠4♣
405♣5♣5♣K♦9♠K♠2♥4♠A♣
413♣3♣3♣2♠5♦2♦5♣3♠2♠
42J♣J♣J♣6♥K♦2♠A♦6♥Q♥
432♣2♣2♣2♣5♣J♦2♦5♠J♣
442♥2♥2♥Q♥K♣5♦A♣2♣8♣
453♥3♥3♥A♠6♣6♠9♠7♠K♦
466♠6♠6♠A♣4♥K♥3♦4♦J♥
47J♠J♠J♠7♥8♥A♣9♦9♠7♥
485♠5♠5♠J♦8♠4♥8♥3♥6♦
499♠9♠9♠4♣3♣8♦10♦7♦2♣
508♦8♦8♦8♠A♣J♠Q♦9♦8♠
513♠3♠3♠K♥J♠9♦A♠Q♣9♥
52A♥A♥A♥9♦4♦A♥J♥9♥8♦
534♥4♥4♥4♠6♥Q♥4♦6♣5♥
54K♦K♦K♦7♦J♣2♥J♦2♦Q♠
55J♥J♥J♥2♦2♠10♣K♥A♥10♣
56K♣K♣K♣7♠3♠6♣K♠10♣9♠
578♥8♥8♥10♣4♣8♥8♠5♣7♦
587♥7♥7♥3♣5♥9♥4♥A♣4♠
59
Sheet3
Cell Formulas
RangeFormula
A6,F6,J6A6=FORMULATEXT(A7)
A7:C58A7=IF(SEQUENCE(,3),ADECK())
F7:H58F7=CHOOSE({1,2,3},ADECK(),ADECK(),ADECK())
J7:L58J7=SWITCH(SEQUENCE(,3),1,ADECK(),2,ADECK(),3,ADECK())
Dynamic array formulas.
 

Attachments

  • Alex Mullen.png
    Alex Mullen.png
    144.3 KB · Views: 12
This is how a new challenge for Alex Mullen looks like. 29 decks. This is surreal !!
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Part 2. Dynamic method. Shuffle n deck of cards, separate decks, each deck a separate column, easy to visualize, like in a memory contest
2The "solution" (Excel's Santa's gift) comes to rescue, this time on a column orientation.check for no dups each deck (column)
3nr. decks29=SUM(BYCOL(B7#,LAMBDA(a,COUNTA(UNIQUE(a)))))/D3=52
4shufflenD4 -if "n" array freezed, if "y" array of D3 decks recalculatesTRUE
5checks if total count of unique elements of each column divided by nr. decks =52
6=LET(n,D3,x,REDUCE(0,SEQUENCE(,n),LAMBDA(v,i,IF(SEQUENCE(,i)=i,ADECK(),v))),IF(D4="y",x,B7#))
7A♥7♣2♣7♦7♣Q♥8♥4♥9♥9♦2♦Q♦9♥10♠A♠2♦6♦6♦9♣10♥9♦2♠7♠A♥J♠4♥2♣9♥2♥
810♥K♠Q♥Q♥Q♠8♥8♣3♥2♠6♥2♣Q♥J♥9♠8♦2♠9♠9♦9♥9♥7♥7♣3♣9♣6♥J♦A♥A♦3♠
96♠8♥K♣K♦9♥Q♣7♦10♣Q♠Q♠K♥4♦5♣7♦7♦J♦Q♥9♠10♠J♦8♠4♦Q♣Q♥10♣3♠9♠J♥10♥
109♣10♦3♠2♠3♦5♠6♣3♣8♣8♥7♥8♣5♠3♦4♥10♥8♦3♦J♥4♣J♠6♠A♠2♠7♦7♦8♦Q♦9♣
118♠Q♦8♥7♠9♦10♥4♦4♠3♠9♣J♣4♥A♣8♥10♥J♥5♥K♥10♦K♦10♠8♠K♣2♥8♣8♥7♦10♠Q♥
124♥4♠A♣7♥J♦J♦K♣7♣2♥J♣8♦3♣2♥Q♦5♠4♠A♦Q♦5♦4♠7♦Q♥Q♠6♦10♦7♣3♥J♣2♠
13J♠3♠7♠J♣7♦4♠8♦5♦J♦Q♦Q♦K♠2♣9♦4♠A♣2♣5♣2♣K♣3♥K♣2♥9♥K♦2♦2♥J♦7♦
14K♦9♠J♦J♥10♣A♥6♠K♠2♦7♦9♠J♦9♣6♣Q♣J♠K♦6♥3♣4♦9♣3♣4♦A♣9♥Q♦J♦10♦7♠
153♠Q♥K♦5♣3♠2♥5♥K♦6♥6♦J♠2♥J♣A♥3♣7♦5♣5♦Q♠7♦3♣K♦10♣4♦3♥Q♥5♦6♣J♦
16J♣J♣10♦5♥A♦8♦Q♥9♥8♦4♦9♥9♦7♠3♣9♠3♣7♣7♥Q♣A♠A♦Q♣9♣7♣J♦6♠K♥5♥5♥
176♥9♥2♦4♠J♥5♦7♠J♠8♥10♣2♥7♣8♥5♠4♦4♦2♦9♣J♦3♦K♥8♦8♠K♠Q♦5♦Q♥K♥6♠
183♥K♥J♠10♦8♦2♠Q♠Q♣7♣9♠A♥9♠7♣J♥9♣5♣10♥10♦Q♥7♠4♣5♣5♥7♠6♦A♠6♣8♥5♣
193♣J♠A♠3♥K♣4♣5♦7♥4♠8♠A♣6♥3♦3♥8♥A♥3♦7♣8♦Q♣3♠2♦9♦8♦8♦2♣6♦K♣8♣
2010♣K♣K♠J♦6♥8♠3♥5♣3♦3♦7♠5♣A♠2♥8♠3♦10♣3♥8♥A♦6♦J♥6♦4♠2♦Q♣A♣7♦A♦
21K♠K♦2♥Q♦5♥Q♠8♠6♥10♠10♠8♣K♥4♣2♦J♥5♥8♣9♥3♥10♠6♣7♠A♦4♣5♣10♣8♠K♠5♦
225♥7♥Q♠9♠5♠A♠5♣6♦6♦2♠9♣2♠6♠7♥7♣8♣2♥Q♣3♦6♣7♣3♠4♠10♦K♠8♣4♠10♣8♠
239♦9♦9♣2♥7♥3♠3♣A♣J♣4♠7♦5♦9♦K♥K♠5♦Q♦3♠10♣3♠2♦A♠8♣K♦6♣2♠5♠6♦8♦
244♠A♣10♣6♦5♦10♦J♥A♥Q♣4♣3♣10♥3♣3♠J♦9♦A♣10♥5♠Q♦K♣10♥3♦10♣4♣10♥K♠3♥K♣
256♦5♦4♠3♣4♦J♥J♦7♦A♦4♥10♠J♥9♠J♣4♣2♣5♦A♣4♣6♠10♦A♦J♠8♣A♦7♥4♣5♣3♣
26Q♦4♥A♥10♥4♣10♠A♥2♣K♦8♣4♠A♦K♥10♣K♦3♥J♠J♠6♠6♥4♠9♥4♣2♣9♠3♣9♣9♠9♦
275♦2♦7♥8♠8♣K♥A♠6♣J♠K♥6♣J♠2♠K♣7♥6♠9♥7♠10♥8♦5♦10♣7♦5♥3♣Q♠J♠6♥J♠
289♠4♦A♦6♠2♦K♦2♣4♦4♣J♠8♠10♣K♦A♠Q♥K♣K♠A♦A♥8♣Q♥J♠8♥6♠3♠8♦3♣2♥A♥
299♥8♦7♣A♥9♣7♦K♦Q♠Q♥10♦5♣Q♠Q♣8♣10♠7♥4♣A♠9♦10♣K♦6♦9♥9♦5♦5♣8♥5♠4♠
30A♠A♥9♠Q♠A♥6♣6♦5♥7♥2♥8♥4♠Q♦6♥2♠Q♥4♥4♠3♠2♣2♠7♥K♦A♦10♠10♠J♣3♣3♦
312♥3♣6♠K♣6♠9♥4♥Q♦7♠3♣3♠A♥10♣6♦6♦6♣7♦8♠A♠K♥2♣2♥8♦8♥2♥K♦A♠2♠4♣
322♠5♥3♣3♠A♣6♠2♦9♣10♥3♥J♥7♥Q♠10♥5♦K♦6♣10♣7♠8♠4♦9♠3♠5♠7♥9♣Q♣8♦K♦
332♣10♠8♠8♥2♣9♣9♠K♥5♥7♣2♠10♠10♠4♣K♥8♥A♠2♣K♦A♣10♣6♣7♥5♣2♠7♠10♣8♠4♥
344♦6♠6♣5♠A♠3♥9♥J♦K♣J♥10♣8♥8♦7♣5♣5♠4♦4♦J♣J♠4♥9♦10♦K♣2♣8♠A♦7♣J♥
352♦8♠J♣6♣4♥A♦A♣10♦3♥J♦5♦2♦6♥4♥Q♠9♠7♥2♦2♥2♠5♥A♣J♣4♥8♠9♦9♥J♠A♣
366♣2♣6♦2♣10♠5♥10♦2♦9♠7♠4♦5♥5♦9♥6♣K♥8♥K♣K♠8♥A♠J♦J♥10♥A♥10♦Q♦4♥2♣
37K♣4♣3♥K♠J♣6♦2♥2♥5♠5♣7♣A♠7♦A♦2♣4♥A♥5♥2♠9♣J♦4♠K♠3♠7♣K♣7♣Q♠3♥
388♣7♠Q♣3♦2♥2♣3♠7♠4♥7♥6♥3♦6♣4♠7♠4♣2♠J♥6♣J♣5♠5♠10♥J♣4♠A♥7♥3♦7♥
39A♦A♠5♦J♠5♣10♣3♦9♠9♦A♥3♥4♣J♠J♦3♥9♣K♥6♠A♦Q♥8♣8♥K♥Q♣6♠6♥2♠4♣Q♣
404♣J♥3♦7♣Q♥3♦2♠2♠6♣Q♣6♦3♥3♥7♠10♦8♦Q♠5♠A♣3♣8♥7♦A♣Q♠5♥4♠5♣K♦K♠
41Q♥10♣8♣9♦8♠4♦10♠8♦3♣8♦Q♥7♦4♦Q♣2♦K♠3♥8♥4♥2♦J♥5♥2♦8♠8♥A♦8♣Q♣10♠
4210♠5♠K♥5♦4♠A♣Q♦8♣Q♦A♦A♦9♣Q♥4♦6♥Q♦J♦K♠K♣4♥Q♠K♥4♥Q♦Q♣3♥9♦2♣7♣
437♠6♦2♠10♣7♠J♠K♥8♥9♣5♠4♣K♦A♦5♦2♥Q♠K♣Q♥7♣7♣A♣4♣A♥7♥4♦J♣5♥A♥Q♠
445♠10♥6♥6♥K♥9♠9♣10♥2♣5♦5♠Q♣7♥5♣Q♦3♠4♠2♠8♠Q♠5♣4♥Q♥K♥9♣9♥2♦2♦6♦
457♣2♠Q♦Q♣3♣3♣A♦K♣A♥10♥5♥3♠8♠2♠10♣10♠9♦7♦J♠9♠7♠10♠3♥6♥7♠5♠4♥5♦5♠
46A♣3♦J♥A♦3♥7♥5♠J♣5♦K♠10♦A♣6♦6♠3♠A♦3♣8♦8♣5♠6♥6♥5♦3♣5♠K♥K♦8♣J♣
4710♦6♥5♣K♥J♠4♥10♥A♦7♦2♣9♦6♦10♥5♥J♠6♦Q♣10♠Q♦7♥J♣Q♦6♥5♦3♦J♥10♦3♠10♦
48J♦Q♠7♦8♦9♠5♣J♣Q♥K♥6♣10♥6♣5♥8♦A♥10♣9♣4♥6♦3♥10♥10♦9♠A♠K♥6♦Q♠10♥6♣
493♦A♦8♦4♥10♥J♣9♦8♠4♦A♣3♦5♠3♠8♠8♣A♠J♣J♦7♥9♦Q♣K♠6♠3♥Q♠5♥4♦9♣10♣
50Q♣3♥4♥9♣K♦2♦J♠5♠A♣Q♥K♠J♣K♣K♠9♥9♥7♠A♥6♥2♥6♠3♦10♠J♠A♠K♠3♠6♠9♠
515♣Q♣5♥4♦K♠Q♦4♣6♠10♦K♣6♠8♦4♥2♣9♦10♦3♠Q♠K♥J♥8♦9♣7♣7♦10♥9♠10♠A♠8♥
528♥5♣5♠2♦Q♦8♣Q♣J♥5♣2♦J♦9♥4♠10♦K♣8♠10♦6♣4♦5♦2♥A♥2♣J♦J♥J♠6♠7♠2♦
537♦8♣4♣A♣8♥6♥K♠A♠6♠K♦Q♣6♠A♥K♦3♦Q♣6♥3♣2♦10♦K♠3♥5♣10♠K♣3♦7♠7♥K♥
54J♥J♦9♦10♠6♣9♦4♠3♠K♠3♠K♣2♣2♦Q♠6♠6♥6♠8♣4♠5♥A♥8♣2♠3♦J♣A♣3♦A♣9♥
558♦2♥4♦9♥Q♣7♣6♥9♦J♥6♠K♦10♦J♦9♣5♥7♠J♥J♣5♣K♠3♦2♣J♦9♠A♣2♥J♥9♦6♥
56Q♠6♣9♥A♠6♦7♠7♣10♠8♠A♠4♥8♠10♦A♣A♣J♣10♠K♦5♥6♦9♥5♦Q♦2♦9♦4♣K♣4♠4♦
577♥9♣10♥4♣10♦K♠10♣3♦10♣5♥A♠K♣K♠J♠J♣7♣8♠2♥9♠A♥9♠Q♠6♣J♥Q♥6♣10♥Q♥A♠
58K♥7♦10♠8♣2♠K♣7♥4♣A♠9♥Q♠7♠8♣Q♥A♦2♥5♠4♣7♦5♣Q♦J♣5♠6♣4♥4♦6♥4♦Q♦
59
ADECK 4
Cell Formulas
RangeFormula
R3,B6R3=FORMULATEXT(R4)
R4R4=SUM(BYCOL(B7#,LAMBDA(a,COUNTA(UNIQUE(a)))))/D3=52
B7:AD58B7=LET(n,D3,x,REDUCE(0,SEQUENCE(,n),LAMBDA(v,i,IF(SEQUENCE(,i)=i,ADECK(),v))),IF(D4="y",x,B7#))
Dynamic array formulas.
 
Shuffling n deck of cards together and dealing them in rounds of k cards/round until we run out of cards.
To check the accuracy of the result array, the total number of occurrences of each card should be equal with the nr. of decks we have started with. ACOUNT was designed for scenarios like this.
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Part 3. Dynamic method. Shuffle n deck of cards, together,dealing them in rounds of k cards. Kind of casino approach.AFLATother functions on minisheet
2Shuffling n=6 decks together and randomly deal all rounds of k=5 card/round until we run out of cards.ARESIZE
3ACOUNT
4Now we can use IF with ADECK()flatten 6 decks will have (rounds can have dups)checking using ACOUNT
5or ADECK(1), randomizing will take place after.6*52=312 cardsshuffling 6 decks togetherdeal rounds of k=5 cards with ARESIZEas we see all unique cards are 6=nr. of decks
6=IF(SEQUENCE(,6),ADECK())=AFLAT(B7#)=RANDARRAY(312)=SORTBY(J7#,L7#)=ARESIZE(N7#,,5)=SORT(ACOUNT(Q7#,2,1))
7J♣J♣J♣J♣J♣J♣J♣0.8064276♦6♦3♦8♠9♣3♠10♠6
8A♦A♦A♦A♦A♦A♦J♣0.2559933♦J♦2♠6♣9♣10♣10♣6
97♥7♥7♥7♥7♥7♥J♣0.7804628♠10♥Q♦2♦7♠9♥10♥6
10A♥A♥A♥A♥A♥A♥J♣0.301579♣5♥10♥8♦4♠A♦10♦6
1110♥10♥10♥10♥10♥10♥J♣0.3834233♠Q♣10♥7♦K♣J♦2♠6
129♥9♥9♥9♥9♥9♥J♣0.216767J♦6♥10♠3♥9♥3♣2♣6
13K♥K♥K♥K♥K♥K♥A♦0.1920332♠9♥9♣8♥Q♠10♠2♥6
14J♠J♠J♠J♠J♠J♠A♦0.7369436♣9♠2♦A♠4♦6♠2♦6
156♦6♦6♦6♦6♦6♦A♦0.2367699♣2♦9♥5♠8♦10♦3♠6
165♣5♣5♣5♣5♣5♣A♦0.58631410♣9♠3♣2♥5♠10♣3♣6
17Q♦Q♦Q♦Q♦Q♦Q♦A♦0.05599910♥2♥4♥3♠10♠7♣3♥6
18A♣A♣A♣A♣A♣A♣A♦0.470341Q♦8♥K♠9♥6♥J♠3♦6
196♠6♠6♠6♠6♠6♠7♥0.7183662♦K♥J♠8♣A♠A♦4♠6
207♣7♣7♣7♣7♣7♣7♥0.322877♠3♠2♦6♠3♣2♦4♣6
214♣4♣4♣4♣4♣4♣7♥0.7711949♥J♣Q♥Q♣5♦K♦4♥6
229♣9♣9♣9♣9♣9♣7♥0.8469385♥10♦2♣8♦A♦8♦4♦6
232♠2♠2♠2♠2♠2♠7♥0.91400610♥4♣6♦Q♥Q♦K♥5♠6
2410♣10♣10♣10♣10♣10♣7♥0.9110688♦2♠J♣6♦4♠4♠5♣6
253♦3♦3♦3♦3♦3♦A♥0.5146864♠5♦J♠7♦7♦6♠5♥6
2610♦10♦10♦10♦10♦10♦A♥0.692273A♦3♦4♥8♣5♠A♣5♦6
27K♣K♣K♣K♣K♣K♣A♥0.584992Q♣4♦3♥4♥5♥J♣6♠6
285♥5♥5♥5♥5♥5♥A♥0.59323810♥7♠7♠6♠3♣6♦6♣6
298♣8♣8♣8♣8♣8♣A♥0.9836217♦8♣3♦7♥6♦8♦6♥6
309♦9♦9♦9♦9♦9♦A♥0.731489K♣9♠K♣K♣5♣9♣6♦6
316♣6♣6♣6♣6♣6♣10♥0.060334J♦4♦7♠2♠9♦2♠7♠6
323♣3♣3♣3♣3♣3♣10♥0.4504396♥Q♠Q♥5♥J♣Q♣7♣6
33Q♥Q♥Q♥Q♥Q♥Q♥10♥0.74654610♠6♣K♥Q♠Q♥3♥7♥6
34Q♣Q♣Q♣Q♣Q♣Q♣10♥0.0265883♥8♣7♣8♠7♣9♠7♦6
356♥6♥6♥6♥6♥6♥10♥0.0516319♥2♠2♣10♣2♥6♠8♠6
36A♠A♠A♠A♠A♠A♠10♥0.5842893♣4♥9♠10♥5♦4♠8♣6
373♥3♥3♥3♥3♥3♥9♥0.5748359♥2♥Q♠3♥2♣A♦8♥6
388♦8♦8♦8♦8♦8♦9♥0.0976739♣9♣4♣5♦K♥Q♣8♦6
39K♠K♠K♠K♠K♠K♠9♥0.1248758♥A♥8♠4♥5♣8♣9♠6
407♠7♠7♠7♠7♠7♠9♥0.178976Q♠J♥5♥4♣7♣5♣9♣6
41Q♠Q♠Q♠Q♠Q♠Q♠9♥0.0915910♠J♠A♣K♠9♥6♣9♥6
424♠4♠4♠4♠4♠4♠9♥0.0498199♠10♥A♥A♦5♥A♥9♦6
432♦2♦2♦2♦2♦2♦K♥0.483662♦10♦K♥7♦9♦K♠A♠6
449♠9♠9♠9♠9♠9♠K♥0.253596A♠9♠10♣6♣K♦5♠A♣6
45K♦K♦K♦K♦K♦K♦K♥0.6021144♦K♣3♥K♣10♦8♦A♥6
464♥4♥4♥4♥4♥4♥K♥0.1889216♠4♦4♥10♠6♥K♠A♦6
47J♥J♥J♥J♥J♥J♥K♥0.8025252♦K♦A♠7♠9♣10♠J♠6
483♠3♠3♠3♠3♠3♠K♥0.3919899♥8♥Q♦5♣8♥3♦J♣6
498♠8♠8♠8♠8♠8♠J♠0.1853235♠J♥2♦J♦Q♥Q♦J♥6
507♦7♦7♦7♦7♦7♦J♠0.7812998♦K♠A♥7♣5♠K♦J♦6
515♠5♠5♠5♠5♠5♠J♠0.55238310♦4♦9♦A♣6♦4♠K♠6
5210♠10♠10♠10♠10♠10♠J♠0.8876059♠K♣7♥3♠4♣3♠K♣6
534♦4♦4♦4♦4♦4♦J♠0.2686383♣Q♠J♦A♥A♦10♣K♥6
542♥2♥2♥2♥2♥2♥J♠0.1902532♥9♦10♥4♣J♦A♠K♦6
555♦5♦5♦5♦5♦5♦6♦0.7129115♠Q♦8♥7♥6♣K♠Q♠6
56J♦J♦J♦J♦J♦J♦6♦0.31008410♣A♣3♠J♣J♠5♦Q♣6
572♣2♣2♣2♣2♣2♣6♦0.3290882♥6♥J♥6♥3♣10♠Q♥6
588♥8♥8♥8♥8♥8♥6♦0.0031014♥K♥3♦10♣J♣7♠Q♦6
596♦0.258773♠A♠2♥6♣6♠10♦
606♦0.24058810♠8♠2♣2♣7♥4♦
615♣0.3480877♣4♣8♠K♦5♠J♠
625♣0.8980278♥Q♥5♣A♣7♦9♦
635♣0.549973K♠2♥7♥7♥4♠J♥
645♣0.6656999♥Q♣J♥7♦7♣K♦
655♣0.9805746♥3♦Q♦5♥2♣8♥
665♣0.521563J♠8♣3♣6♥3♥2♠
67Q♦0.659746K♥5♦5♣Q♣9♦10♦
68Q♦0.681618J♠A♥J♥A♣8♠Q♠
69Q♦0.9440268♣J♦A♠
70Q♦0.248347A♠
71down toQ♦0.764906A♦down to
72312 rowsQ♦0.0281283♠312 rows
73↓↓↓↓A♣0.5634692♦↓↓↓↓
74A♣0.7100226♠
ADECK 5
Cell Formulas
RangeFormula
B6,W6,J6,L6,N6,Q6B6=FORMULATEXT(B7)
B7:G58B7=IF(SEQUENCE(,6),ADECK())
J7:J318J7=AFLAT(B7#)
L7:L318L7=RANDARRAY(312)
N7:N318N7=SORTBY(J7#,L7#)
Q7:U69Q7=ARESIZE(N7#,,5)
W7:X58W7=SORT(ACOUNT(Q7#,2,1))
Dynamic array formulas.
 
ANDECK([n],[k],[r]) The function.
Shuffles "n" decks of cards, 2 ways, separately (memory contest approach), or all together and deals "k" cards/round for a nr. of "r" rounds (casino approach).
Functionality:
- if k=0 or omitted, shuffles "n" decks of cards, separately, 1 deck/column, columns will have no dups (memory contest approach).
- if k>0, shuffles "n" decks of cards together in a single "pile", and extracts (deals) out of it, a nr. of "k" cards/round for a number of "r" rounds. (columns can have dups)(casino approach)
Arguments:
[n]: nr. of decks to shuffle
[k]: nr. cards to deal/round
[r]: nr. of rounds when k>0, (decks shuffled together), or, when k=0 first "r" rows of the array of decks shuffled separately
Excel Formula: 
=LAMBDA([n],[k],[r],
    LET(c,SEQUENCE(,n),s,SEQUENCE(r),
      d,REDUCE(0,c,LAMBDA(v,i,IF(SEQUENCE(,i)=i,ADECK(),v))),
      f,AFLAT(d),a,ARESIZE(f,,k),
      IFS(n=0,ADECK(),k=0,IF(r,INDEX(d,s,c),d),r=0,a,TRUE,INDEX(a,s,SEQUENCE(,k)))
   )
)
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1arguments scenarios
2nr.decksnr.cardsnr.rounds·1·2·4check
3[n][k][r]Descriptionn,k,r omittedn,3,k,r omittedn,3,k,5,r omittedcount unique=n
4·10001 deck shuffled=ANDECK()=ANDECK(3)=ANDECK(3,5)=ACOUNT(S5#,2,1)
5·2n00n decks shuffled separately2♦6♠7♣5♥K♣7♥K♣2♥J♦K♣3
6·3n0rn decks, r rows of. whole array6♠3♣9♦2♠J♣6♠J♠6♦K♦7♥3
7·4nk0n decks, k cards, all rounds3♥5♦10♥Q♥9♠4♠5♦5♣Q♠2♥3
8·5nkrn decks, k cards, r rounds8♠9♥8♥5♣4♣J♥7♠A♠8♠J♦3
95♣10♥4♦10♣K♦7♥Q♦A♠J♣J♣3
10·33♠Q♦6♠J♥6♠8♠10♥8♦4♥6♠3
11n,3,k,,r,5K♦A♣8♦9♥A♥3♠A♥9♥A♥J♠3
12=ANDECK(3,,5)A♠8♣Q♥4♣10♥Q♦8♣J♦6♥6♦3
132♦10♥9♦K♣10♠J♥4♦4♠5♥Q♥7♣4♦K♦3
142♠3♦10♣2♥7♣4♣6♥3♣10♠J♥9♦9♣9♠3
15A♥Q♦Q♠9♦10♣A♣Q♣10♣8♠6♦Q♣7♠4♠3
169♠4♦10♦A♣5♥8♠4♥A♦9♠6♣5♥5♣5♦3
173♥5♣7♥7♠9♠K♠7♦4♥A♣6♣J♠K♣5♣3
18A♦6♥A♥8♣3♠4♦10♦2♣2♦Q♠3
19·5Q♦8♠Q♣K♦10♥8♥8♦2♦8♦4♣3
20n,3,k,5,r,79♥K♠9♣10♠10♣Q♥6♥5♠4♦J♥3
21=ANDECK(3,5,7)K♠5♣5♠J♣3♣4♠2♥5♠8♣7♠3
22A♦4♠3♦6♠7♠roundsQ♠Q♥10♦K♠4♥Q♦8♥K♠K♥A♠3
23K♥7♦2♥8♣Q♥J♥J♥7♥9♠7♦5♦2♠9♣9♦8♠3
248♣6♦7♣9♥3♠9♣9♦5♣5♦7♣7♣6♥4♣2♠Q♦3
257♠3♥A♠10♣Q♦Q♣A♠6♥A♠7♠A♦10♦Q♣10♦10♥3
267♥8♣A♥4♣8♦7♦4♣9♠A♥6♦Q♠A♣A♣5♠8♦3
27K♠K♠3♣A♠J♥10♠J♦8♣10♥3♥Q♣K♦9♣9♠4♥3
285♥6♠6♣6♥9♦10♣2♥5♥Q♠3♥J♠A♦3♣6♠A♥3
29c a r d s8♥7♦7♠K♣3♠7♦5♦Q♠K♥3♠3
302♠4♠5♦A♣2♥3♥A♠K♠5♥9♥3
31K♥9♣3♥10♦3♦Q♥3♦2♠8♣8♣3
3210♦J♠3♦A♦2♣9♥9♥J♦4♣6♥3
332♣2♠K♦7♥10♠J♥6♣9♦K♥5♥3
344♠3♥6♣2♦J♣7♥10♠10♣7♦Q♥3
358♣10♦Q♦3♥K♠2♣3♦5♣8♥7♣3
364♦2♣2♠6♠2♦4♦3
377♥4♥A♦8♦3♣3
38J♠7♠J♠Q♦10♠3
396♥3♦4♥9♣9♦3
40Q♥5♠2♦6♦9♣3
415♠K♦A♠8♠10♣3
4210♥6♣10♣K♥Q♣3
433♦8♦K♥2♥A♦3
444♣2♦2♣4♠6♣3
459♠A♦10♠6♣A♣3
464♥8♥2♥2♣10♦3
476♦K♣6♦3♦2♣3
486♣A♥K♣J♦2♦3
497♣Q♠J♦3♣8♥3
508♦K♥3♣J♠5♠3
51J♦3♠7♦3♠K♠3
525♦J♣4♠5♠K♥3
53J♣7♥3♠8♥7♦3
543♣6♦J♣9♦2♠3
555♥4♦Q♠7♠3♥3
56A♥Q♣9♥7♣3♦3
57
ANDECK 1
Cell Formulas
RangeFormula
L4,O4,E21,E12,Y4,S4L4=FORMULATEXT(L5)
L5:L56L5=ANDECK()
O5:Q56O5=ANDECK(3)
S5:W36S5=ANDECK(3,5)
Y5:Z56Y5=ACOUNT(S5#,2,1)
E13:G17E13=ANDECK(3,,5)
E22:I28E22=ANDECK(3,5,7)
Dynamic array formulas.
 
Forgot to mention ANDECK calls ADECK AFLAT , ARESIZE
Other functions on mini-sheet ACOUNT
 
Last edited:
Checking the functionality, the differences between shuffling decks separately and all together, realized that something that was covered in the concept design was not reflected in the formula.
Shuffling n decks together requires creating a "pile" of all decks with AFLAT that needs another shuffling with SORTBY(f,RANDARRAY(ROWS(f)) . This part was missing.
ANDECK([n],[k],[r]) Calls ADECK AFLAT , ARESIZE. Other functions on mini-sheet ACOUNT.
Excel Formula: 
=LAMBDA([n],[k],[r],
    LET(c,SEQUENCE(,n),s,SEQUENCE(r),
       d,REDUCE(0,c,LAMBDA(v,i,IF(SEQUENCE(,i)=i,ADECK(),v))),
       f,AFLAT(d),a,ARESIZE(SORTBY(f,RANDARRAY(ROWS(f))),,k),
       IFS(n=0,ADECK(),k=0,IF(r,INDEX(d,s,c),d),r=0,a,TRUE,INDEX(a,s,SEQUENCE(,k)))
    )
)
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Differences between shuffling separately and all togetherother functionsfor both shuffling methods
2-checking the differences between ANDECK(5) and ANDECK(5,5)ACOUNTnr. of occurrences of all cards
3(k=0, shuffling sep. ; k<>0 shuffling together)should be = nr of decks
4dealing 5 decks shuffled together=AND(V8:V59=5,Y8:Y59=5)
55 decks shuffled separatelyin rounds of 5cards/round, all roundschecking for dups by clmsTRUE
6no dups on columnsdups on columns possibleall values=52 => no dups
7=ANDECK(5)=ANDECK(5,5)=BYCOL(B8#,LAMBDA(a,COUNTA(UNIQUE(a))))=SORT(ACOUNT(B8#,2,1))=SORT(ACOUNT(H8#,2,1))
82♠9♠K♠4♣J♥3♣4♦7♦4♥6♦525252525210♠510♠5
9A♠K♠7♣2♣J♦9♣A♠Q♦Q♥A♥10♣510♣5
103♣2♦A♠10♠8♠5♦7♣K♥Q♦6♥values <52 => clms have dups10♥510♥5
119♠A♣7♠5♦9♠J♠2♠3♥J♠8♥=BYCOL(H8#,LAMBDA(a,COUNTA(UNIQUE(a))))10♦510♦5
12J♦J♣5♥Q♥J♠Q♥A♥3♦2♣8♥33373838342♠52♠5
13J♣6♦8♠6♣A♥7♠6♠J♣4♥5♥2♣52♣5
143♥A♠3♥2♦5♠A♣5♣3♠K♦3♦2♥52♥5
155♥10♥J♥K♣3♥2♦7♠A♠6♥2♣2♦52♦5
164♣J♦10♥3♠10♠7♥7♣A♦8♠K♠3♠53♠5
179♥2♥K♣3♦4♠2♦Q♠K♠Q♠8♦3♣53♣5
18K♥K♦Q♠8♣K♣4♠7♠6♦2♥A♥3♥53♥5
197♦3♥5♦4♥4♦4♠4♥8♣3♥5♠3♦53♦5
204♥5♥K♥6♦8♥K♣J♠10♠4♠5♣4♠54♠5
21A♣2♣7♥10♣10♣J♦4♣10♦10♠10♣4♣54♣5
228♣6♠4♣8♦7♣5♣2♣10♦10♠6♦4♥54♥5
232♦4♠4♠K♦A♣9♣A♣10♥3♠K♠4♦54♦5
249♦8♥6♥K♠Q♣A♥J♦7♠6♥10♣5♠55♠5
25A♦9♥6♦5♠5♣9♠5♥K♦5♠3♥5♣55♣5
262♥4♦3♣A♥7♦9♣J♣7♥8♣6♠5♥55♥5
277♠8♣3♦J♣7♠4♦5♠5♠4♥5♥5♦55♦5
288♦10♣J♣9♥2♥A♣4♦5♥J♦2♦6♠56♠5
293♠4♣8♥A♦6♠10♦9♥Q♦6♣8♥6♣56♣5
308♠10♦K♦7♥4♥8♠9♦8♦K♦6♦6♥56♥5
31J♠K♣2♠10♦Q♠J♦8♠8♠8♦7♦6♦56♦5
3210♦4♥7♦7♠A♦Q♥K♦10♥8♣Q♦7♠57♠5
3310♣J♥9♥4♦6♥Q♠7♥6♠J♠A♦7♣57♣5
347♣8♠2♦9♣A♠4♠4♦6♣4♣Q♣7♥57♥5
359♣3♦2♥10♥Q♦4♥9♥K♥2♣5♠7♦57♦5
36Q♣J♠9♣8♥5♥J♣3♦2♠10♥6♦8♠58♠5
37K♦K♥Q♣2♥7♥8♦2♥5♣3♣8♦8♣58♣5
38Q♠6♣9♠6♥9♥J♥Q♠3♣Q♣3♥8♥58♥5
396♦2♠Q♦J♥4♣9♦7♣10♠2♠Q♠8♦58♦5
408♥Q♦A♦8♠9♦3♣A♦9♠7♦10♣9♠59♠5
416♣5♣A♥7♦K♠K♣5♦J♠4♦Q♦9♣59♣5
4210♥A♦5♠5♥2♣7♥K♦8♥9♥J♦9♥59♥5
432♣5♠4♥9♦2♦K♠10♣3♦Q♣7♠9♦59♦5
44K♠7♣J♠9♠8♦10♥K♣A♣5♥K♣A♠5A♠5
455♣7♥A♣7♣Q♥A♦4♣9♣J♥10♥A♣5A♣5
467♥10♠10♣4♠10♦10♦K♣9♥10♣2♥A♥5A♥5
476♥5♦6♣3♣K♥A♦6♥Q♣6♣5♦A♦5A♦5
48Q♥3♣2♣A♣6♦7♦3♣10♠Q♣9♣J♠5J♠5
495♦7♠8♣Q♣3♠4♠K♥K♥5♦8♠J♣5J♣5
5010♠A♥Q♥J♦6♣2♠7♣3♦A♣8♣J♥5J♥5
51K♣Q♥J♦3♥2♠6♠3♥J♥K♠J♣J♦5J♦5
52J♥Q♣5♣K♥9♣7♣3♠6♥9♦2♠K♠5K♠5
533♦6♥10♦A♠5♦2♥8♥6♠8♣K♥K♣5K♣5
54A♥Q♠9♦J♠K♦9♠6♣J♥5♣A♠K♥5K♥5
556♠7♦6♠Q♠3♣7♥A♠9♦2♦Q♥K♦5K♦5
564♠9♣4♦Q♦3♦4♣9♦Q♥10♦J♣Q♠5Q♠5
575♠9♦10♠5♣J♣J♥7♦3♠6♣5♦Q♣5Q♣5
584♦8♦3♠6♠8♣4♣2♥9♠9♠A♥Q♥5Q♥5
59Q♦3♠8♦2♠10♥2♦9♥A♠3♠2♣Q♦5Q♦5
60
ANDECK 2
Cell Formulas
RangeFormula
U4,N11,N7,U7,X7,H7,B7U4=FORMULATEXT(U5)
U5U5=AND(V8:V59=5,Y8:Y59=5)
B8:F59B8=ANDECK(5)
H8:L59H8=ANDECK(5,5)
N8:R8N8=BYCOL(B8#,LAMBDA(a,COUNTA(UNIQUE(a))))
U8:V59U8=SORT(ACOUNT(B8#,2,1))
X8:Y59X8=SORT(ACOUNT(H8#,2,1))
N12:R12N12=BYCOL(H8#,LAMBDA(a,COUNTA(UNIQUE(a))))
Dynamic array formulas.
 
What about slot machines? (named also fruit machines) ?
Fun fact from Wikipedia: "Slot machines are also known pejoratively as one-armed bandits because of the large mechanical levers affixed to the sides of early mechanical machines and the games' ability to empty players' pockets and wallets as thieves would."
A classical mechanical slot machine has "n" reels (3, 5 or more), each reel has same nr. of symbols.
Also, there is a "window" on which we can see 3 or more "lines"(rows) at a time,
The middle line is called payline.
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1Slot machines. Concept
2If we "unfold" the reels in columns shape, a 3 reels slot machine with 10 symbols each, perfectly aligned will look like this:
3(1-10 represents the index values of the 10 symbols)
4
5all reels alignedsimulate "spinning" the reels with random values
6111is like adding this values to each column (10 as top random value induces a "360º" spin)
7222=RANDARRAY(,3,1,10,1)
8333266to ensure that
9444index order is keptsingle cell formula for all steps
10555=A6#+E8#=MOD(E11#-1,10)+1=MOD(SEQUENCE(10)+RANDARRAY(,3,1,10,1)-1,10)+1
11666377377437
12777488488548
13888599599659
1499961010610107610
1510101071111711871
1681212822982
17913139331093
1810141410441104
19111515155215
20121616266326
21
22reelall reels spined randomlyfor a "window" that has 3 lines
2310 symbols=INDEX(A24:A33,O11#)=INDEX(E24#,SEQUENCE(3),SEQUENCE(,3))
24???????
25???????← pay line
26???????
27???BAR
28????Only thing left is hitting F9 to spin the reels
29????
30?BAR??
31??BAR?
32????
33BAR???
34
35Note that on mechanical reels, order is kept
36
ASLOT 1
Cell Formulas
RangeFormula
A6:C15A6=SEQUENCE(10)+SEQUENCE(,3)^0-1
E7,L23,E23,J10,O10,E10E7=FORMULATEXT(E8)
E8:G8E8=RANDARRAY(,3,1,10,1)
E11:G20E11=A6#+E8#
J11:L20J11=MOD(E11#-1,10)+1
O11:Q20O11=MOD(SEQUENCE(10)+RANDARRAY(,3,1,10,1)-1,10)+1
E24:G33E24=INDEX(A24:A33,O11#)
L24:N26L24=INDEX(E24#,SEQUENCE(3),SEQUENCE(,3))
Dynamic array formulas.
 
ASLOT(ra,[nr],[nl]) The function.
ra: reel array (vertical 1D array)
[nr]: nr. reels: if 0 or omitted nr=3, or 3<=nr<=rows(ra)
[nl]: nr. lines: 0 or omitted, all reels revealed, or 1<=nl<=rows(ra)
Excel Formula: 
=LAMBDA(ra,[nr],[nl],
    LET(r,ROWS(ra),w,MEDIAN(3,nr,r),l,MEDIAN(0,nl,r),
       a,INDEX(ra,MOD(SEQUENCE(r)+RANDARRAY(,w,1,r,1)-1,r)+1),
       IF(l,INDEX(a,SEQUENCE(l),SEQUENCE(,w)),a)
    )
)
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1ASLOT. The function
2
3reel arraynr,nl,omitted =>nr,5,nl,omittednr,omitted,nl,3nr,omitted,nl,1
4?=> nr=3, nl=rows(ra)=> nl=rows(ra)=> nr=3=ASLOT(A4:A16,,1)
5?=ASLOT(A4:A16)=ASLOT(A4:A16,5)=ASLOT(A4:A16,,3)???
6????????????
7??BAR??BAR??????nr,5,nl,7
8????????BAR???=ASLOT(A4:A16,5,7)
9??????BAR???????
10????BAR????nr,omitted,nl,5BAR???BAR
11?BAR???????=ASLOT(A4:A16,,5)?????
12?????????????????
13?????????????????
14???????????BAR?????
15?????????????????
16BAR??BAR???BAR????
17????????
18????????
19
20wrong arguments
21nr,omitted,nl,20nr,4,nl,-2
22nr,20,nl,3 if nr>rows(ra)=>nr=rows(ra) max nr=rows(ra)nl>rows(ra)=>nl=rows(ra)nl<0=>nl=0=>nl=rows(ra)
23=ASLOT(A4:A16,20,3)=ASLOT(A4:A16,,20)=ASLOT(A4:A16,4,-2)
24????????????BAR???????
25?????BAR??????????BAR???
26???????BAR??????BAR?????
27?????BAR?
28nr,-3,nl,4 if nr<0=>nr=3???????
29=ASLOT(A4:A16,-3,4)??BAR????
30??????????
31BAR?????????
32???BAR??????
33???????BAR??
34???????
35??????BAR
36???????
37
ASLOT 2
Cell Formulas
RangeFormula
S4,C29,X23,R23,C23,N11,S8,N5,H5,C5S4=FORMULATEXT(S5)
S5:U5S5=ASLOT(A4:A16,,1)
C6:E18C6=ASLOT(A4:A16)
H6:L18H6=ASLOT(A4:A16,5)
N6:P8N6=ASLOT(A4:A16,,3)
S9:W15S9=ASLOT(A4:A16,5,7)
N12:P16N12=ASLOT(A4:A16,,5)
C24:O26C24=ASLOT(A4:A16,20,3)
R24:T36R24=ASLOT(A4:A16,,20)
X24:AA36X24=ASLOT(A4:A16,4,-2)
C30:E33C30=ASLOT(A4:A16,-3,4)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,686
Messages
6,126,202
Members
449,298
Latest member
Jest

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