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
Happy Holidays 2023!! New Xmas-tree design.
Distinctive design concept. Occupies only 2 columns.(left clm or the clm where we write the formula should be right aligned)
Previous designs required individual cells for each ornament.
A natural tree has uneven branches lengths. To create randomly this effect, we only have to use chars of different widths.
As ornaments array, any clm vector can be used with any chars. (if array we use TOCOL)
Our old "stored" and dusted ornaments lambda box AO()
AO()
Excel Formula: 
=LAMBDA(LET(q,SEQUENCE(44,,10035),UNICHAR(IF(q>10058,q+117698,q))))
AXMAS(h,o,[s ])
h: trunk's height (total height will be h+3, 1 for the base and 2 for the top ornament)
o: ornaments array , clm vector
s: branch or "step's" height (creates the "slope" of the contour, bigger value, higher angle), if omitted step 1
Excel Formula: 
=LAMBDA(h, o, [s],
    LET(
        i, SEQUENCE(, h) <= QUOTIENT(SEQUENCE(h) - 1, MAX(s, 1)) + 1,
        a, LAMBDA(BYROW(IF(i, INDEX(o, RANDARRAY(h, h, 1, ROWS(o), 1)), ""), LAMBDA(x, CONCAT(x)))),
        VSTACK({"⭐", "⭐"; "‖", "‖"}, HSTACK(a(), a()), {"🎁▓", "▓🎁"})
    )
)
Note: If you have new eta lambda possib. (do not have this update yet) lambda(x,concat(x)) can be replaced with concat
=LAMBDA(h, o, [s ],
LET(
i, SEQUENCE(, h) <= QUOTIENT(SEQUENCE(h) - 1, MAX(s, 1)) + 1,
a, LAMBDA(BYROW(IF(i, INDEX(o, RANDARRAY(h, h, 1, ROWS(o), 1)), ""), CONCAT)),
VSTACK({"⭐", "⭐"; "‖", "‖"}, HSTACK(a(), a()), {"🎁▓", "▓🎁"})
)
)
Xmas tree 23.xlsx
ABCDEFGHIJKLM
1!!! Always left clms, right aligned !!!!
2
3ornaments same width , h=30,s=2single ornament"natural" effect 2 simple ornaments, different widthsornaments diff. widths, h=30, s,omitted
4=AXMAS(20,SEQUENCE(9),2)=AXMAS(20,"+",2)=AXMAS(25,{"+";"·";"i"})=AXMAS(25,{"i";"o";"-"})
5⭐⭐⭐⭐⭐⭐⭐⭐
6‖‖‖‖‖‖‖‖
777++··io
814+++··+oo--
99575++++i·+++·-oi--i
109658++++·+······o--oioo-
11884132++++++i·+·iii···i-oioi-io-
12165775+++++++++·+i+····iiioo--oi-iio
1321442346+++++++++··++·+iii·+··iii--oo-iiooii
1421462548++++++++··iii·++·+··+iiio-o-oiooooooi--o
154865621594+++++++++++++··+iiiii··+·ii·-i-ii-oio-oioioioi
165345226984++++++++++·+·++··ii+·+i·i+++i·oioio-o-iiii-iiooi--
17594349241324++++++++++++·+++··+·+·++·····ii·iioii-ooooioioiiioio----
18465857634513++++++++++++++++i··+iii·+++·+ii·i·+·--oi-i-oioio-ii---oo-ooi
1946336896137976++++++++++++++ii++·+iiii·ii·+++++·+·+i+ioo--i--oi-oiii-oiiiii--ioo
2075955813786458++++++++++++++··ii··i·+i·+i··+ii+ii··+i·iiooi---oiioi-io---i-i-o--oioi
213263481797816954+++++++++++++++++·i+iiii····i+·++i+i·i+i·iii··iiiiioo-oi-iioooooioi-oi-iooo-
226524287953227339++++++++++++++++i··i+++iiii+·i·i++·i·+·+i·i+··i+-o---o-ooooo-oioioo-o-ioiooooiii
23987452293975755257++++++++++++++++++··++··i+ii+·i·++·i+··++··i+++·i+·iioi----ioo--i-iiooiooo-ii--o-oioo-
24587552751634635279++++++++++++++++++·+·ii·iiiii+i+··i··+i·i····+·++iii+·-io-o--ooiiiiioi--o-o-i-o-ooioiioooo
2518271943284962416345++++++++++++++++++++++·+i+ii+++·iiii·+·i·i++i+++i··i++i··ioioo-oioo-iiooo-ii-io-o-io-o--iioooiio
2631524637166528492699++++++++++++++++++++·+·+··i+i··+·+i+iii++i+ii·i··++·ii+ii++i-ii---oi-ii-i-iiiooooiiiii-oiii-oi-o-io-
27🎁▓▓🎁🎁▓▓🎁+i+ii+···i·+·ii++iii+·i·iii+·ii··+·i++·+·iooiiio---oioioii-o-iii-io-ooioii-i--i--io-
28+i·i+··ii·++··+··+iii·++····+i+·iiiii·i·i·+iioioiii-ii---o--oo-iio-----ioio--o--o-oio-i-
29i+i·+·+++·+·+i··+++·+·++i·+i+ii+·i++++··++·+iii-i-iioo-ooi--iiooooiooooo-o-ooi-oio----o-iiio
30·iiii··ii·ii+i·ii··+iiiiiii·+·iiii+i··+++·+i+ii·ooiioiiii-oii-oo-oo-ioi--ooioioooo-oiioi---o-oio
31·+·+i·++·ii+iii+++i++i·+··i++·+ii·i··i++·+ii·+·i+i-ioo-i--o-i-oooiiiio-ooo--iooiooo-ioi-i-oio-iiiooo
32🎁▓▓🎁🎁▓▓🎁
33
Intro
Cell Formulas
RangeFormula
B4,K4,H4,E4B4=FORMULATEXT(B5)
B5:C27B5=AXMAS(20,SEQUENCE(9),2)
E5:F27E5=AXMAS(20,"+",2)
H5:I32H5=AXMAS(25,{"+";"·";"i"})
K5:L32K5=AXMAS(25,{"i";"o";"-"})
Dynamic array formulas.

Other examples ornaments arrays:
Xmas tree 23.xlsx
ABCDEFGHIJKLMN
1
2lambda that embeds a formula for ornaments
3 - array of ornaments AO()
4↓↓↓↓ - defined names
5=AO()orn_aorn_bunichar values of accessories/trunk
6✳⓫𝓐𝓑𝓒𝓓star top⭐11088
7✴⓬𝓔𝓕𝓖𝓗upper trunk‖8214
8✵⓭𝓘𝓙𝓚𝓛trunk▓9619
9✶⓮𝓜𝓝𝓞𝓟present box🎁127873
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🌚
44🌛
45🌜
46🌝
47🌞
48🌟
49🌠
50
ornaments arrays examples
Cell Formulas
RangeFormula
B5B5=FORMULATEXT(B6)
B6:B49B6=AO()
M6:M9M6=UNICODE(L6:L9)
Dynamic array formulas.

Various Tree designs:
Xmas tree 23.xlsx
ABCDEFGHIJ
1(zoom out hit F9)
2=AXMAS(23,AO(),2)=AXMAS(23,AO(),3)=AXMAS(23,SEQUENCE(20),2)
3⭐⭐⭐⭐⭐⭐
4‖‖‖‖‖‖
5❇❅🌕❄211
6✻✶🌓❀312
7✼❊✵🌟✽🌍21228
8🌑✾🌟🌒✾🌠❄🌝176319
9🌞✿✿✷🌍❆✿❇🌙❆1411191013
10❆🌐🌓✶🌎🌔❉❇❁✻9614181914
11❀🌠❁🌐🌞✵🌒✻✾❉❀✻🌖✵15204917131114
12🌑🌝🌍❀🌏❁🌖✻🌓✵🌓✺🌗🌓10447191399
13🌘✽✸🌠🌒❃❇🌓❂🌟🌜✿❈🌏🌒🌘2101912849118
14❇❂✾🌒✸❇🌏✼❁✼❇❂✷🌛🌑🌝🌐❉12420181075119
15🌕🌑🌟🌚✺✳❃🌏❀🌝✴❅❈🌓🌐✽✾🌎❈🌔21213614151716209812
16🌞❀🌏🌟🌝✵✽🌏❃❇❄🌘🌟✺❄🌛🌙🌕✹🌎11818142811109182
17🌠✼🌗❇🌗✾🌗❅🌕🌠✷❂❂✶🌑🌟✴🌏🌜❁🌓❂🌗❁17513198818531681920
18✿❀✼🌘🌖🌔🌓✽❆🌓🌍🌠🌠❃🌘✳❁❅❄🌚🌚❁🌔❇191126415726157111416
19🌛🌗🌕❅🌗🌑🌞🌛🌎🌚🌓🌞🌚❁🌏🌒🌞🌒✼❈✵✳✾🌛🌠❀4911105118142011741679
20❊🌙🌜✽✵✽🌛🌍🌗✶🌟❅🌟✿❉🌝✻❊✻🌙✶🌞🌎❊❊🌛🌑🌟4481135141552161418151820
21🌏❃✾🌍🌟✷❆✵🌎✴✽🌟🌗✴🌒❂🌍❀✾🌔🌟🌎❄🌘🌍🌍🌞✴✸🌐9176491991813913511852126
22✸🌖✺✾🌎🌛✵🌓✶✸🌜✷🌝🌏✴❁❈❈🌖✻✽✾❀🌗❇❇✼🌎🌑🌖17351113182920615114561816
23✺❊❅🌓❃🌘🌏🌔❅❁🌙✹✿✽✹❈❃🌗❂🌏❃✾❄✽🌕🌘🌚❅✹🌖🌛✴🌜🌖10842814418764512153132013184
24🌔🌖✽🌕✾❄❆❀🌐🌝🌐🌓🌗🌖❇✹✺❆✾✾✶🌐🌐✵✾🌓🌖🌔✵🌔✹✹✴🌜31131316216915121020191415131758
25🌝✻🌔🌒✶✵✺🌛❅✼🌐✾✴✸🌓✽❀🌠❉🌜🌚🌔🌓❂🌓🌘✷❉❆✳✺🌘❅✽🌟✳17141118201012159418181113992010146420
26✳✳✾🌖✽🌓🌐🌘❁🌗✵🌚🌝🌟🌕✺🌛🌠❁🌜🌠🌖🌏❁🌏🌒🌑🌒✻🌜✹✹✴✶✷🌒🌘❄1132096519210189151821319713341415
27🌍✷✴🌕🌎❅🌍🌏✶🌔✻❆✶🌛🌞🌐🌙❊✴🌒❄🌏🌞🌚❉❃❉🌙❀❂❇✶🌞✸✾🌑❀✾🌒❊93201751516151115719172141321017217134
28🎁▓▓🎁🎁▓▓🎁🎁▓▓🎁
29
tree designs
Cell Formulas
RangeFormula
B2,H2,E2B2=FORMULATEXT(B3)
B3:C28B3=AXMAS(23,AO(),2)
E3:F28E3=AXMAS(23,AO(),3)
H3:I28H3=AXMAS(23,SEQUENCE(20),2)
Dynamic array formulas.

Xmas tree 23.xlsx
ABCDEFGHIJ
29
30ASCII Xmas tree
31=AXMAS(23,TOCOL(orn_b),2)=AXMAS(23,orn_a,2)=AXMAS(23,CLEAN(CHAR(SEQUENCE(255))))
32⭐⭐⭐⭐⭐⭐
33‖‖‖‖‖‖
34𝔁𝓸⓴◓oð
35𝔁𝓯◕⑮N|XN
36𝓪𝓠𝓳𝓘◓⑭⓮⓱¾ÍVv-"
37𝓤𝓙𝓾𝓰◒⑬⑰◓èÒ›JÕ
38𝓸𝓺𝓮𝓰𝓕𝓔⑰◓⓱⑳◕⓲Ò/ÎÙû$£WÀ
39𝓐𝓻𝓤𝓮𝓻𝓞⓫🌍⓰⓮⓴⑰ÞvlÃÿu»$mo
40𝓐𝓗𝓾𝓺𝔁𝓮𝓝𝓼⑯⑪◒◓⓳⑳⓳⓰Ç^áJð½jçˆpR
41𝓠𝓼𝓗𝓟𝓕𝓞𝓲𝓟⓯⑪⑳⑮⑳⓰⑲⓭“¥+É"`h…_¨oeùÞn
42𝓡𝔀𝓴𝓮𝓮𝓾𝓧𝓮𝓥𝓟⓮⑳◒◑⓲◎⑰◔🌎◓Ûø†ª?L_ÚY4µÉK¹Q
43𝓘𝓭𝓜𝓤𝓐𝓑𝓽𝓮𝓵𝓨⑮◎⓱⑯⓭⓬⓫⓳⑬⓰»D“.zaFap•éAÀ£•¢4Ü¿
44𝓔𝓱𝓯𝓫𝓷𝓢𝓯𝓘𝓣𝓥𝓤𝓻⓳●⓲⓲●⑭⑰⑱◕⑫⓫⑮s¤Ž _Rt÷ÜIn˜ó!ן‘¯´
45𝓛𝓔𝓿𝓬𝓸𝓶𝓢𝓹𝓩𝓧𝔁𝓤⓭◎⑪⓬◑⑲⑭◐⓯⓱⑪⓴ŒHÉãáÚñæRÒüª°ª™þNLs=
46𝓺𝓡𝓡𝓭𝓼𝓑𝓿𝓮𝓒𝓵𝓺𝓢𝓰𝓗⓲⑮🌎⓭⓯◉⑮◓⑲◕🌎◑⑮⑳Jë8„Dï$MóE&¢0ÌŒØPÛƒ±
47𝓑𝓟𝓝𝓛𝓠𝓳𝓻𝓟𝓙𝓨𝓑𝓖𝓙𝓒◕⓲◉⑯⑭⑬⑮◕⓳⓮◐🌎◒⑯¶2"RT¶(èÃFk‚ÉOK¦‚¿õŠ‹"75ö
48𝓨𝓢𝓷𝓲𝓜𝓓𝓮𝓧𝓰𝓔𝓪𝓤𝓦𝓛𝔂𝓸⑯⑳⑪⓮⓬⓰⑲⑭◓⓭🌍⑭🌎⓳⑱⑭šIÓ@ݬó˜«eEX8Ž·`z«ÃNeçVj
49𝓡𝓕𝓟𝓢𝓯𝓔𝔀𝓢𝓙𝓻𝓦𝓤𝓷𝓞𝓐𝓯⑱⓬⓲◐⓲⓬◉🌍⑭●⑫⓳⑳◎🌎◑g¸w®µ`ößµ!6¢HêqÓÄ#‹¯¯êÙïB>Äè
50𝓪𝓾𝓱𝓫𝓑𝓞𝓟𝓔𝓦𝓴𝓖𝓪𝓬𝓩𝔀𝓱𝔂𝓑⑮◐⓳◉⓫⑱⑮⑰◕🌍⓯⑪◉⑮⓯◎◓⓲““M³ÝSÜìÇ6¦TFЄ‚p"çç¤Mdgät¶@y
51𝓳𝓝𝓽𝓬𝓿𝓿𝓴𝓬𝓙𝓼𝓡𝓢𝓸𝓧𝓼𝓤𝓛𝓜◔⑳⑮⑱⓰⑰●⓮◐⑮◓⑮⑯⓯⓰●◑⓰ö„á–¥U{¹èÎi.¹VbjXœïöýEøÌIÅ]ê
52𝓫𝓑𝓑𝓲𝓞𝓳𝔃𝓕𝓝𝓯𝓛𝓺𝔀𝓱𝓴𝓾𝓒𝓪𝓬𝓢⓳⑯⑳⓳🌎⓲⓱⑯⓱⓴⓰◑⑳⓭⑮⑬⓳🌍⑳◕ãÅU$P#M±NRˆm…Zp›Jýë7ÒÉXÕZNßí¿Ý€WÒ
53𝓚𝓩𝓱𝓰𝓭𝓖𝓥𝓼𝓒𝓢𝓖𝓲𝓿𝓼𝓛𝓼𝓽𝓓𝓤𝓮⑳◕🌎◎⓯⓱◔⑪◓🌎⑰⑬●⑯●⓯🌎⓰◎●îö¾¥Eâ5[®Bô`?c4"{?ç¤òza`jy{úÆì½³§wèšÄ
54𝓿𝓠𝓑𝓑𝓙𝓫𝓦𝓰𝓿𝓞𝓺𝓝𝓪𝔀𝓔𝓤𝓠𝓩𝓔𝓖𝓛𝓝◒⓰⓬🌍⑬⓮⑪⓳⑭◓◑⑯⓭⓮⓳●◐🌍⑪◉⑫●J˵ÎjâÕD|'÷Æɵ껈炵Q¬°þ¢€®fæ‰dÖC/‡Y¡
55𝓻𝓜𝓼𝓐𝓞𝔀𝓳𝓥𝓱𝓤𝓾𝓬𝓽𝓳𝓑𝓤𝓻𝓳𝔃𝓾𝓔𝓢◎⑭⑫⓰⑲◑⑮⓮⑪⓭⓳⓭⓰⓬◐⓰⓴⓬⓮◒⓲⑰Jå”IÞ?Uoçºás_AªqÅÒ¨.Å}Л+œýwD6<80–H†Ô
56𝓛𝓲𝓻𝓦𝓼𝓟𝓰𝓦𝓸𝓥𝓦𝓤𝓦𝓶𝓧𝓔𝓝𝓧𝓮𝓟𝓦𝓡𝓝𝓨◑⓭⓬⓫●⑪◔⑬⓫⓬●⑬⑬◒⑯⑭⓯⑮⓮◑⑱⓴◓⓫ìBi¨0ɺ}M«Q*_³A1ù”€¹EOlRÛa¡3 8pƒ)DÄG[“6t
57🎁▓▓🎁🎁▓▓🎁🎁▓▓🎁
58
tree designs
Cell Formulas
RangeFormula
B31,H31,E31B31=FORMULATEXT(B32)
B32:C57B32=AXMAS(23,TOCOL(orn_b),2)
E32:F57E32=AXMAS(23,orn_a,2)
H32:I57H32=AXMAS(23,CLEAN(CHAR(SEQUENCE(255))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
orn_a='ornaments arrays examples'!$D$6:$D$36E32
orn_b='ornaments arrays examples'!$F$6:$I$18B32
 
Bonus!! Xmas trees and "event" functions FREEZE and POP
FREEZE(fm,cl,[k]) will stop the formula fm in cell cl from recalculating even if it contains volatile functions.
Excel Formula: 
=LAMBDA(fm,cl,[k],IF(cl#=0,fm,IF(k,fm,cl#)))
fm: formula to "freeze"
cl: cell address where we write the formula (creates circular reference so iterative calc should be enabled: File->Options->Formulas->Enable iterative calculation)
k: if omitted, formula freezes, if 1 , still recalculates
Excelambda Xmas tree 2023 Bonus.xlsx
ABCDEFGHIJK
7
8cl,C10,k,omittedcl,G10,k,1
9=FREEZE(RANDARRAY(3,2),C10)=FREEZE(RANDARRAY(3,2),G10,1)
100.474170.9814090.8648440.768964
110.8310440.7340750.1771140.670154
120.6054020.4431150.9571510.081149
13
14!!! If we hit F9 left array does not recalculate, right one does because k=1
15At one time, if we want to freeze the right one also, we simply delete the "1"
16
17The only way to make left array to recalculate is to click the cell, like for editing and
18hit Enter or Ctrl Enter
19This method should be used succesivly until the formula delivers a result you want to keep.
20
21Notes: Versatility of FREEZE
22Inserting rows or columns at the upper or left size of top left cell will not affect the freeze.
23Moving the cell with freeze will keep the freeze to the new location.(cell address is relative)
24
Bonus-FREEZE
Cell Formulas
RangeFormula
C9,G9C9=FORMULATEXT(C10)
C10:D12C10=FREEZE(RANDARRAY(3,2),C10)
G10:H12G10=FREEZE(RANDARRAY(3,2),G10,1)
Dynamic array formulas.

- edit and Enter, edit and Enter (or Ctrl Enter and F2) ....successively until the formula delivers one you'd like to keep
Excelambda Xmas tree 2023 Bonus.xlsx
KLM
2
3FREEZE with AXMAS
4hint: wrote the formula, then...edit,Enter,edit Enter…until I wanted to keep this one
5=FREEZE(AXMAS(30,AO(),2),L6)
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❊✽🌍🌘🌗✻❄🌐✸✶✼❄❉❂✳✵❊✷✳🌎🌍❃❂❁
32❃🌘🌛🌖❆🌠❆❆❅🌛🌗🌓🌑✳❅❅❆✾🌗✽✽❇❈🌐✻❀
33✳❆✳🌎🌙🌓✻🌏❅❄❂❊🌕🌔❂✷🌘✺🌠❆🌘✺✳🌗✵❆
34🌒✴✿🌐❈❂🌟✳🌠❃✿✵✹❆🌗❄🌔✷✽🌙❊🌐❊❇🌞✾🌐🌞
35🌞❇❄🌝❄🌕❉🌔🌖🌖✻✻✷🌞❃🌘✵🌖✶❇🌛🌓🌚❉✸🌕❆✸
36🌑✴🌠✿❆🌐✹🌞🌖✵✼❉❆❅🌚🌖🌛✴❅❆🌛🌞✵✼🌜🌞🌒🌗❁✴
37🌙❈🌞❂✶🌟🌎❂❃❀❂🌜✺❈✼✶❇❅🌛✻❄🌔✷❅🌍🌓🌓✽✺🌗
38🎁▓▓🎁
39
Bonus-FREEZE
Cell Formulas
RangeFormula
L5L5=FORMULATEXT(L6)
L6:M38L6=FREEZE(AXMAS(30,AO(),2),L6)
Dynamic array formulas.


POP(if,then,cl) calls FREEZE
Excel Formula: 
=LAMBDA(if,then,cl,FREEZE(IF(if,then,0),cl))
if: condition to trigger the pop
then: the formula that will "pop" into "existence" when the condition is met (else is always 0, to not pop)
cl: cell address where the formula resides (circular reference => iterative calc should be enabled: File->Options->Formulas->Enable iterative calculation)

!!! After the "pop", even if the condition changes or is deleted, the "pop" will stay.
The pop cell will show a 0 if the condition is not met => for surprize effect you can cond form the cell to not show the 0
(We can not use "" instead of 0, 0 is subtle important for FREEZE inner mechanism)
Excelambda Xmas tree 2023 Bonus.xlsx
ABCDEFGHIJ
11
12DIY Excersise: pop a message when cell 9 will receive a value
13
14=POP(ISNUMBER(B23)," Congrats, you have 9 sales!!!",D15)
151200
16230
17340step1: fill the cells until you reach cell 9
18450nothing happens
19517
20623step2: set a nr. In cell 9
217the mess pops up
228
239step3
24delete cell 9, mess will stay
25
26to do it again, click pop cell to edit and Enter, you are reset
27
Bonus-POP
Cell Formulas
RangeFormula
D14D14=FORMULATEXT(D15)
D15D15=POP(ISNUMBER(B23)," Congrats, you have 9 sales!!!",D15)


Excelambda Xmas tree 2023 Bonus.xlsx
OPQRSTUVWXYZ
1!!! Play with your date time in the formula to see the tree pop !!!!
2POP with AXMAS: make a minimalistic Xmas tree pop after a date and time (as soon as spreadsheets calculates anything, after the specified time)
3Will choose the cell P6, cond formated not to show 0 (white ink when 0)
4(for american date system use month first 12-24-23 12:00:00)
5=POP(NOW()>--"24-12-23 14:10:00",AXMAS(20,{"+";"*";"x";"X";"·";"º";"o";"O";"~";"%"}),P6)
6⭐⭐
7‖‖
8+*
9··*O
10ºX~ºo%
11~+xXOº+x
12x%O%x·o·%+
13*º%+··ºo·%Oo
14·ººº%Oº*º*%*oº
15*º*%*XoOo·oOºoo+
16o+*~xXxxOX·O+*+Oox
17xX+xXX+**%~*ºXXXoºOX
18%%ºx·%··º%X~*·+x·++xºX
19XX·xº··+·º*X+%++~~%·+Xxº
20*º%*%xXx~Xx~%ºOO·*X~x%·%·X
21O*~·%*XX*x*º+OX*O+xxoO+x*++O
22*x·oº~O~~*~o+Oº·x+~*º%ºxoo%+oO
23o~~+OX+%oOXº··%+ox*·%%ºOOox+*+xO
24~X*xXO~X+º%~+oº*xX%xºº*~%~~º~+·O·X
25%OoXx%O·º·%Xoo+~ooXo~Ox%*+*X*+xº*O*~
26*OX*XxoO+%*~*%%+O+ºoºOoox··ºo%x*O%*%%~
27ºº%x*º~X·%xO·ººo%º*ºº·x~~ººxX·o%ºOº*%oxº
28🎁▓▓🎁
29
Bonus-POP
Cell Formulas
RangeFormula
P5P5=FORMULATEXT(P6)
P6:Q28P6=POP(NOW()>--"24-12-23 14:10:00",AXMAS(20,{"+";"*";"x";"X";"·";"º";"o";"O";"~";"%"}),P6)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,891
Messages
6,127,602
Members
449,388
Latest member
macca_18380

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