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
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Ornaments array in ascending order, includes geometrical shapes, fruits, sweets, sports, toys, objects, pets …etc
2
3⏩⏪⏫⏬⏩ 9193⏪ 9194⏫ 9195⏬ 9196=AXMAS(30,5,A3:D42)=AXMAS(20,4,A3:D42)
4♔♕⚓⚡♔ 9812♕ 9813⚓ 9875⚡ 9889 ⭐ ⭐
5⚽⛄✅✳⚽ 9917⛄ 9924✅ 9989✳ 10035▒▒
6✴✵✶✷✴ 10036✵ 10037✶ 10038✷ 10039✹?
7✸✹✺✻✸ 10040✹ 10041✺ 10042✻ 10043❄?
8✼✽✾✿✼ 10044✽ 10045✾ 10046✿ 10047⏫?❇⏫
9❀❁❂❃❀ 10048❁ 10049❂ 10050❃ 10051??????
10❄❅❆❇❄ 10052❅ 10053❆ 10054❇ 10055??????
11❈❉❊❌❈ 10056❉ 10057❊ 10058❌ 10060???⏪??
12❎⭐⭕?❎ 10062⭐ 11088⭕ 11093? 127744??✴????✼
13????? 127757? 127758? 127759? 127760????⏩⏩??
14????? 127761? 127762? 127763? 127764❀❃❈✾?✷???✅
15????? 127765? 127766? 127767? 127768??✵????✳?♔
16????? 127769? 127770? 127771? 127772???⚡??????⏫?
17????? 127773? 127774? 127775? 127776✺???✅???????
18????? 127792? 127793? 127794? 127799✅???✻?❊?????
19????? 127800? 127801? 127802? 127803?♕?????❈✼✻???❀
20????? 127804? 127805? 127806? 127807???❄⛄?????????✺?
21????? 127808? 127809? 127810? 127811???⛄?⭕?❅???????❅
22????? 127812? 127813? 127815? 127816?❈❉??⭕?????????⏩
23????? 127817? 127818? 127819? 127820????????✼✻?????✾
24????? 127821? 127822? 127823? 127824???????✸❊?????
25????? 127825? 127826? 127827? 127851??⛄???✳✅?
26????? 127852? 127853? 127870? 127871❌✿???????
27????? 127872? 127873? 127874? 127875????❁??✳?
28????? 127876? 127877? 127878? 127879????❆?⚽??
29????? 127880? 127881? 127882? 127883??✺??⚓??⭐??
30????? 127890? 127936? 127944? 128030??????✅?⏩??
31????? 128036? 128037? 128038? 128039❉?✹???✳✻??⏩
32????? 128040? 128125? 128141? 128142??❎?❊??????
33????? 128151? 128152? 128153? 128154?❈??❊?????⚓
34????? 128155? 128156? 128157? 128158???????
35????? 128159? 128160? 128161? 128162
36????? 128205? 128269? 128270? 128302
37????? 128303? 128308? 128309? 128310
38????? 128311? 128312? 128313? 128314
39????? 128315? 128508? 128509? 128756
40????? 128757? 128758? 128759? 128760
41????? 128761? 128762? 128763? 128764
42????? 129351? 129352? 129353? 129358
43
AXMAS 2
Cell Formulas
RangeFormula
F3:I42F3=A3:D42&" "&UNICODE(A3:D42)
K3,X3K3=FORMULATEXT(K4)
K4:U34K4=AXMAS(30,5,A3:D42)
X4:AF24X4=AXMAS(20,4,A3:D42)
Dynamic array formulas.
 
Noticed that making the chars bold, they apear in black and white (like Windows computers show them, no color).
AXMAS.xlsx
HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1Other ornaments array, letters, numbers
2
3??????????????????????????
4?????????????????????????
5①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳
6❶❷❸❹❺❻❼❽❾❿⓫⓬⓭⓮⓯⓰⓱⓲⓳⓴
7?✳✴✵✶✷✸✹✺✻✼✽✾✿❀❁❂❃❄❅❆❇❈❉❊
8??????????????????????????
9
10=AXMAS(30,4,H3:AG8)
11 ⭐section of an array with blanks
12▒=AXMAS(20,3,H5:AF7)
13❇ ⭐
14?▒
15?❄?⓭
16?⑫?❀❸✽
17⑰⑭?⑲⑲⓲
18??✺⑰⑥❃
19?⑫??③✻⓬✵❆✷
20?✺?④?❇❆❸❽⓰
21⓬?⑯⑤?⓳❀✷❄✸
22???⓲?⓬⓭⓱⑰❷✽④
23②?❷⑳❸⑮?❶①⓮✾❾✹⑨
24?❺????❅⑲③❆?⑩⓰⑱
25✺?✵?⑮⓲?❾❼❄⓭⓫❼⓰⓰✾
26?✾⓰????✳⑪⓬⑭❶❽✷✶❺
27?⑧⑪??????❁❸③✵❀❹❷✷⑭
28?✷??????✶①⑩❸⓳❂❁❹❅✴✺⑮
29?⓱⑰??⑫⑰??✴⑱❃❇⓮⓭✳❿⓳⑲✼
30⓮??❿④?✵?⑪❁⑫✳❷✳❂⑲⑰❃✷❸
31⑫❆❃✸?❅⑳⑳❉??⑪✼❈✿❆⑩⑬✻⓴❿✷✸❺
32?✶??✾?⓱?⑯✹?⑩✽❆❸⑪①⓬✽⑫⑨❊①⓱
33???⑰?❾??⓭??❾✷⑲❻✻⓯⓫①⓯✻②❇❸
34①??❼?????✶❼?????????
35???✶⓫⑤?❾❀⑤⓲❆?
36?⑪??⑩①✶??⑳✾❊❽
37?②??⑰⑤?⓳④⑥???
38❈③❀?⑫⑦?✺④✽?❀?
39④⑯??✷?⑪⓫?⓯❾❈???
40❻✻❁⑤???❀??✶✻⓲✳⑲
41②???✺???✴⑨⓴???❾
42❇???⓲⓲⑳④⓯?❿?⑪?❶
43???????????
44
AXMAS 3
Cell Formulas
RangeFormula
I10,Z12I10=FORMULATEXT(I11)
I11:W43I11=AXMAS(30,4,H3:AG8)
Z13:AL34Z13=AXMAS(20,3,H5:AF7)
Dynamic array formulas.
 
AXMAS with numbers as ornaments array and CF, icon sets, data bars, color scales.
To get rid of the number values, for icon sets check "show icons only", for color scales and data bars, custom number formatting, at "type" field only ";"
=AXMAS(30,5,SEQUENCE(50))
Unfortunately, here, on mini-sheet do not show. On spreadsheet looks very nice, especially the data bars. Uploaded (inserted) some captures. ✌️
data bars color scales.png

icons set.png
 
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1AX(ht,sh) no ornaments array, does not call any lambda
2- this is the part of the function that embeds the ornaments values array, for that reason, the formula does not need AFLAT anymore.
3….q,SEQUENCE(44,,10035),o,UNICHAR(IF(q>10058,q+117698,q))….
4This was possible because I found 2 large enough segments of consecutive unicode values,
5that also look nice as ornaments.
6
7=LET(q,SEQUENCE(44,,10035),UNICHAR(IF(q>10058,q+117698,q)))
8✳
9✴=AX(30,5)=AX(30,4)
10✵ ⭐ ⭐
11✶▒▒
12✷??
13✸?✽
14✹❆✿✾?
15✺✶?✳❊❆✻
16✻✴??✼?❅
17✼❊✾???❇
18✽?✴✷?❁?✿?
19✾✶❆✳?❉??✺
20✿✸✼❈✵❆?✺✵❅❇
21❀✸✿???❂❀✴??
22❁❈?????????❅?
23❂??✻✻✴✾??✷✶??
24❃❄✽❄?✶❇✴?❅??❀
25❄❃??✺?❊✶???❃???
26❅❉❅✵?❈❉?✸??❊❀???✳
27❆❂✾✵❃❃❇❁✳✴❇❁?❆?✸✸
28❇❇❂✹❊?❂??????✹❀✽✽
29❈✺?✺✹?????❇??❂???
30❉✺???❈?✳✶❇???✳?❃??✳✼?
31❊✿✹✺?✸❇?❀???✺❁?✻?✽❈❊❁
32?❁❂?✸✻❁???❅❊?❃?✹✷????
33?✼?❁?✳✺✸✵?????✸?❄❅❁??
34?✸✴?✵?✺❆❅?✸❊????❄✷❈❉❉?❉
35??❃❊??✽????❂?✿???✼✿?✿❇?✺❇
36?❁???✵??✵?✺❄❂?✺?❃❊?✿✳✿❈❆✽
37?✸❉❇??❊?✷????✿✶??❇✼✹❀❃??❊
38??✿?✹❉❀????✿???❁??????✹?✼❅✸
39?✻???❀❃??❊❇✽??✴✳✼❁❅❄??❅????
40????????✵✶❈?✿❉✹?✿❃??❊?❃
41?✽?❄??✹❇❈❈??❂✻✴❂
42????????????
43?
AXMAS 5
Cell Formulas
RangeFormula
B7,R9,E9B7=FORMULATEXT(B8)
B8:B51B8=LET(q,SEQUENCE(44,,10035),UNICHAR(IF(q>10058,q+117698,q)))
E10:O40E10=AX(30,5)
R10:AF42R10=AX(30,4)
Dynamic array formulas.
 
Writing an argument free lambda that can store values with formulas. LAMBDA(LET(...
AO() - Array Ornaments
Excel Formula: 
=LAMBDA(LET(q,SEQUENCE(44,,10035),o,UNICHAR(IF(q>10058,q+117698,q)),INDEX(o,SEQUENCE(4,11))))
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Crazy scenario for fun: Using AX embedded ornaments array with AXMAS function.
2
3We can write an argument free lambda called AO()
4
5=LAMBDA(LET(q,SEQUENCE(44,,10035),o,UNICHAR(IF(q>10058,q+117698,q)),INDEX(o,SEQUENCE(4,11))))()
6✳✴✵✶✷✸✹✺✻✼✽
7✾✿❀❁❂❃❄❅❆❇❈=AXMAS(23,4,AO())
8❉❊????????? ⭐
9???????????▒
10✾
11Note: Added the INDEX at the end to see it in a more compact way,✽
12AXMAS being able to deal with any arrays.?❇❅
13❊?✺
14=AO()storage box✵??
15✳✴✵✶✷✸✹✺✻✼✽❃??
16✾✿❀❁❂❃❄❅❆❇❈❉✽❇?✻
17❉❊?????????❄❂❁?❆
18???????????❄???✹
19??❇?✽
20We can consider Array Ornaments lambda, AO() ,??✾❃✿✿?
21the storage box that saves them for next years use.?????❅✸?
22?❁?✷❈❄✷
23?✻✴❃???
24❃?✷✶✷??✺?
25✺?✾❂??❁??
26❉❄✸?❉??❉?
27?✶✿✴??✾❇❁
28✾✸✻❆✹?✾?❈✹?
29✿❀✵✾?✽?????
30✳???❇???❂??
31❇?✶???????✼
32???????
33
AXMAS 6
Cell Formulas
RangeFormula
B5,B14,R7B5=FORMULATEXT(B6)
B6:L9B6=LAMBDA(LET(q,SEQUENCE(44,,10035),o,UNICHAR(IF(q>10058,q+117698,q)),INDEX(o,SEQUENCE(4,11))))()
R8:AB32R8=AXMAS(23,4,AO())
B15:L18B15=AO()
Dynamic array formulas.
 
Everybody is free to believe in whatever they consider. Me, I believe in Santa, Excel's Santa. So, the other day, I wrote him a letter. It was a secret wish, but now I am going to disclose it. I've wished for more recursive iterations. Very anxious, when I woke up this morning, I run under my Xmas tree to open the present boxes, hoping that I will find at least "1000 iterations". Instead, look what I found. ?
AXMAS.xlsx
ABCDEFGHIJKLMNO
1
2
3 ⭐
4▒
5✾
6?
7?❂❉
8❅?❉
9?❉❉
10✿✻?
11??✻?✳
12?????
13????✻
14?✻???
15✺✵?✸❉✿✽
16???✵?✾✺
17✺❇???✹❀
18✳??✸✹?✿
19❊?✸✹✴?✶✶?
20???✷?❉❂✷✼
21??????❂??
22❃❀❄?✳❄❂✷?
23❀✶✻???❈❅❁??
24✾??✾✶❄✻?❅✳✻
25???✿❄?✳???✷
26????✻?❂✹?✶✵
27✻✿?❆❊❊???✻✵❇✾
28✵✻?✿❁?❄?❀?❊??
29❇❃???❄❄✾??❄✿?
30?❂✴?✾?✻?✹??✵?
31#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
32
33I know what Santa did, he hacked my AX function, just at the beginning,
34=LAMBDA(ht,sh,LET(t,UNICHAR(11088),i,UNICHAR(9618),p,0^0,….,
35and replaced the present boxes unichar value "p" with 0^0 ☹️☹️
36
Sheet6
Cell Formulas
RangeFormula
B3:N31B3=LAMBDA(ht,sh,LET(t,UNICHAR(11088),i,UNICHAR(9618),p,0^0,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,"")))(25,4)
Dynamic array formulas.
 
As I've shared with you, I did not receive anything good from Excel's Santa, but, I've missed a present box, and….what I found inside is amazing.
It's a solution for something that has been bothering me since day one of the new lambda helper functions. The fact that BYROW or BYCOL do not spill arrays, only single values.
Solved it before though, with an "external" function as "accumulator" (latest posts ASCAN's thread). This solution does not need any. Good Excel's Santa after all. ?✌️?
AXMAS.xlsx
ABCDEFGHIJKLMNOPQR
1
2asort ascending byrow does not spill
323114=BYROW(B3:F5,LAMBDA(a,SORT(a,,1,1)))
478598#CALC!
564463
6
7The Solution
8sort ascending "byrow"
9=LET(a,B3:F5,r,ROWS(a),s,SEQUENCE(r),REDUCE(0,s,LAMBDA(v,i,IF(SEQUENCE(i)=i,SORT(INDEX(a,i,),,1,1),v))))
1011234
1157889
1234466Works!!!
13
14unique "byrow"
15same formula, we only have to replace function SORT with UNIQUE
16=LET(a,B3:F5,r,ROWS(a),s,SEQUENCE(r),REDUCE(0,s,LAMBDA(v,i,IF(SEQUENCE(i)=i,UNIQUE(INDEX(a,i,),1),v))))
172314
187859
19643#N/AWorks!!!
20
21this is the construction that works without any other "accumulator", performing calculations "byrow" that can spill.
22 =REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(SEQUENCE(i)=i, function(INDEX(a,i,)),v)))
23This is the solution I found in the present box!!!! ✌️✌️
24
25Defining a custom made lambda helper "byrow" that spills (name it AROW),
26needs a lambda as argument that embeds the function that instructs REDUCE what to perform. (like in our examples)
27
28if y(x)=LAMBDA(x,function(x)), then:
29AROW(a,y)=LAMBDA(a,y,LET(r,ROWS(a),s,SEQUENCE(a),REDUCE(0,s,LAMBDA(v,i,IF(SEQUENCE(i)=i,y(INDEX(a,i,)),v)))))
30Calling it =AROW(a,LAMBDA(x,function(x))) will work for any function(x)
31In other words, whatever results y(x) can spill (horizontally) for a single row,
32AROW will do it "byrow", for each row of an array "a"
33
34Ofcourse "y" can represent lambdas with more arguments, will see how to instruct reduce to handle them.
35
AXMAS gift
Cell Formulas
RangeFormula
I3,B16,B9I3=FORMULATEXT(I4)
I4I4=BYROW(B3:F5,LAMBDA(a,SORT(a,,1,1)))
B10:F12B10=LET(a,B3:F5,r,ROWS(a),s,SEQUENCE(r),REDUCE(0,s,LAMBDA(v,i,IF(SEQUENCE(i)=i,SORT(INDEX(a,i,),,1,1),v))))
B17:E19B17=LET(a,B3:F5,r,ROWS(a),s,SEQUENCE(r),REDUCE(0,s,LAMBDA(v,i,IF(SEQUENCE(i)=i,UNIQUE(INDEX(a,i,),1),v))))
Dynamic array formulas.
 
Ornaments "storage" array AO() was not the first lambda argument free function. First was about creating a chessboard ACHESS()
For fun, I found a way to be able to play chess. Other functions on mini-sheet ACHESS, AROTATE (last post AFLIP thread)
Other ideas that can use lambda argument free functions could be shuffling n decks of cards or rolling n dices.
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
11. All 4 chessboard possible orientations, CF "pattern" conceptCF "pattern" rules a1=h8=black=TRUE ; h1=a8=white=FALSE
2CF formula (drag and drop)
3CF formula concepta8=153=odd=white=false=ISEVEN(a8)
4=ACHESS()=UNICODE(B5:B12)+UNICODE(C13:J13)=ISEVEN(UNICODE($B5)+UNICODE(C$13))
58♜♞♝♛♚♝♞♜153154155156157158159160FALSETRUEFALSETRUEFALSETRUEFALSETRUE
67♟♟♟♟♟♟♟♟152153154155156157158159TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
76151152153154155156157158FALSETRUEFALSETRUEFALSETRUEFALSETRUE
85150151152153154155156157TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
94149150151152153154155156FALSETRUEFALSETRUEFALSETRUEFALSETRUE
103148149150151152153154155TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
112♙♙♙♙♙♙♙♙147148149150151152153154FALSETRUEFALSETRUEFALSETRUEFALSETRUE
121♖♘♗♕♔♗♘♖146147148149150151152153TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
13abcdefgh
14
15=AROTATE(ACHESS(),1)a1=146=even=black=true=ISEVEN(a1)
16 12345678=UNICODE($B17)+UNICODE(C$16)=ISEVEN(UNICODE($B17)+UNICODE(C$16))
17a♖♙♟♜146147148149150151152153TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
18b♘♙♟♞147148149150151152153154FALSETRUEFALSETRUEFALSETRUEFALSETRUE
19c♗♙♟♝148149150151152153154155TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
20d♕♙♟♛149150151152153154155156FALSETRUEFALSETRUEFALSETRUEFALSETRUE
21e♔♙♟♚150151152153154155156157TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
22f♗♙♟♝151152153154155156157158FALSETRUEFALSETRUEFALSETRUEFALSETRUE
23g♘♙♟♞152153154155156157158159TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
24h♖♙♟♜153154155156157158159160FALSETRUEFALSETRUEFALSETRUEFALSETRUE
25
26h8=160=even=black=true=ISEVEN(h8)
27=AROTATE(ACHESS(),-1)=UNICODE($J28)+UNICODE(B$36)=ISEVEN(UNICODE($J28)+UNICODE(B$36))
28♜♟♙♖h160159158157156155154153TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
29♞♟♙♘g159158157156155154153152FALSETRUEFALSETRUEFALSETRUEFALSETRUE
30♝♟♙♗f158157156155154153152151TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
31♚♟♙♔e157156155154153152151150FALSETRUEFALSETRUEFALSETRUEFALSETRUE
32♛♟♙♕d156155154153152151150149TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
33♝♟♙♗c155154153152151150149148FALSETRUEFALSETRUEFALSETRUEFALSETRUE
34♞♟♙♘b154153152151150149148147TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
35♜♟♙♖a153152151150149148147146FALSETRUEFALSETRUEFALSETRUEFALSETRUE
3687654321
37
38=AROTATE(ACHESS(),2)h1=153=odd=white=false=ISEVEN(h1)
39hgfedcba=UNICODE($J40)+UNICODE(B$39)=ISEVEN(UNICODE($J40)+UNICODE(B$39))
40♖♘♗♔♕♗♘♖1153152151150149148147146FALSETRUEFALSETRUEFALSETRUEFALSETRUE
41♙♙♙♙♙♙♙♙2154153152151150149148147TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
423155154153152151150149148FALSETRUEFALSETRUEFALSETRUEFALSETRUE
434156155154153152151150149TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
445157156155154153152151150FALSETRUEFALSETRUEFALSETRUEFALSETRUE
456158157156155154153152151TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
46♟♟♟♟♟♟♟♟7159158157156155154153152FALSETRUEFALSETRUEFALSETRUEFALSETRUE
47♜♞♝♚♛♝♞♜8160159158157156155154153TRUEFALSETRUEFALSETRUEFALSETRUEFALSE
48
49general formula
50=iseven(unicode($horiz)+unicode(vert$))
51
ACHESS CF
Cell Formulas
RangeFormula
B4,V39,M39,B38,V27,M27,B27,V16,M16,B15,V4,M4B4=FORMULATEXT(B5)
B5:J13B5=ACHESS()
M5:T12M5=UNICODE(B5:B12)+UNICODE(C13:J13)
V5:AC12V5=ISEVEN(UNICODE($B5)+UNICODE(C$13))
B16:J24B16=AROTATE(ACHESS(),1)
M17:T24M17=UNICODE($B17)+UNICODE(C$16)
V17:AC24V17=ISEVEN(UNICODE($B17)+UNICODE(C$16))
B28:J36B28=AROTATE(ACHESS(),-1)
M28:T35M28=UNICODE($J28)+UNICODE(B$36)
V28:AC35V28=ISEVEN(UNICODE($J28)+UNICODE(B$36))
B39:J47B39=AROTATE(ACHESS(),2)
M40:T47M40=UNICODE($J40)+UNICODE(B$39)
V40:AC47V40=ISEVEN(UNICODE($J40)+UNICODE(B$39))
Dynamic array formulas.
 
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
12. Playing chess
2step 2. copy the board as values
3step 1. pick board orientationstep 3. CF the board =ISEVEN(UNICODE($M6)+UNICODE(N$5))
4=AROTATE(ACHESS(),1)
5 1234567812345678
6a♖♙♟♜a♖♙♟♜
7b♘♙♟♞b♘♙♟
8c♗♙♟♝c♗♙♗♞♟♝
9d♕♙♟♛d♕♙♟♛
10e♔♙♟♚e♔♙♟♚
11f♗♙♟♝f♙♘♟♝
12g♘♙♟♞g♙♟♞
13h♖♙♟♜h♖♙♟♜
14
15step 4. moving pieces example:
16e2-e4 => copy e2, right click e4, hit V (copy as value), delete e2
17This method keeps CF in place
18
19Note: we can not select a cell and move it with the mouse,
20because will disrupt CF
21
ACHESS play
Cell Formulas
RangeFormula
B4B4=FORMULATEXT(B5)
B5:J13B5=AROTATE(ACHESS(),1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N16,N6:U13Expression=ISEVEN(UNICODE($M6)+UNICODE(N$5))textNO
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,892
Messages
6,127,610
Members
449,389
Latest member
ChessManNaill

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