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
FYI: Another video from MrExcel about the new functions:
www.mrexcel.com

PRODUCTIF With MAP REDUCE or SCAN - 2416

Three more of the new Lambda Helper functions in Excel: MAP, REDUCE, and SCAN. In today's episode, see three different ways to create a PRODUCTIF function in Excel using MAP, REDUCE, or SCAN. Important points: MAP can take one or more arrays. MAP is looking at each individual cell, not just the...
www.mrexcel.com www.mrexcel.com
 
smozgur said:
FYI: Another video from MrExcel about the new functions:
www.mrexcel.com

PRODUCTIF With MAP REDUCE or SCAN - 2416

Three more of the new Lambda Helper functions in Excel: MAP, REDUCE, and SCAN. In today's episode, see three different ways to create a PRODUCTIF function in Excel using MAP, REDUCE, or SCAN. Important points: MAP can take one or more arrays. MAP is looking at each individual cell, not just the...
www.mrexcel.com www.mrexcel.com
Click to expand...
Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
Function arguments field works fine, took the brackets. ✌
 
Xlambda said:
Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
Function arguments field works fine, took the brackets. ✌
Click to expand...
Thanks for the heads up. I just made the necessary changes to allow brackets and edited the AGGR function post.
 
Check this out, another cool excel challenge from today's Jon-Excel Campus YT: Excel Formula Challenge: Even or Odd License Plates
I was missing the "elegance" of recursive functions.
EO(c) Even/Odd function. recursive !!!
c: single value, single cell reference
Excel Formula: 
=LAMBDA(c,
    LET(
        n, LEN(c),
        x, --RIGHT(c, 1),
        IF(n = 0, "Odd", IF(ISERR(x), EO(LEFT(c, n - 1)), IF(ISODD(x), "Odd", "Even")))
    )
)
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLMNO
1License Plate ChallengeRule: If last digit of license plate is odd nr. than "Odd", else "Even", if no digits found => "Odd"
21st solution2nd solution, entire clm vector, all steps
3=REDUCE(A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))
4=EO([@[Plate Number]])↓↓↓=--RIGHT(D6#,1)=IF(ISODD(+F6#),"Odd","Even")
5Plate NumberOdd/Even↓↓↓↓↓↓↓↓↓=IFERROR(H6#,"Odd")
6123M57Odd123577OddOdd
71COU886Even18866EvenEvencheck results, both solutions
81D48821Odd1488211OddOdd=AND(J6#=tblChallenge[Odd/Even])
91DVT189Odd11899OddOddTRUE
1021W7454Even2174544EvenEven
1127U17Odd27177OddOdd
122IGR878Even28788EvenEven
132RDZ434Even24344EvenEven
142SWQ185Odd21855OddOdd
153FII697Odd36977OddOdd
163QOW866Even38666EvenEven
173RQC229Odd32299OddOdd
183RSZ114Even31144EvenEven
194FXL296Even42966EvenEven
204KUC981Odd49811OddOdd
214QKB212Even42122EvenEven
224QRG241Odd42411OddOdd
234RVF139Odd41399OddOdd
245ACY568Even55688EvenEven
255IYF234Even52344EvenEven
265KFC128Even51288EvenEven
275NFD419Odd54199OddOdd
285RXA984Even59844EvenEven
295TGB871Odd58711OddOdd
305UEZ777Odd57777OddOdd
315WKT763Odd57633OddOdd
326LJT722Even67222EvenEven
336MVB117Odd61177OddOdd
346XZR265Odd62655OddOdd
3571F27UJOdd71277OddOdd
36745Y4IEven74544EvenEven
3776736QEven767366EvenEven
387FOB933Odd79333OddOdd
397JCM371Odd73711OddOdd
40884PCXEven8844EvenEven
418JHC777Odd87777OddOdd
428RDQ178Even81788EvenEven
438TMW513Odd85133OddOdd
449PMH329Odd93299OddOdd
459UGW414Even94144EvenEven
469UUL349Odd93499OddOdd
47BEACHOdd#VALUE!#VALUE!Odd
48G97C8TEven9788EvenEven
49H1R226IEven12266EvenEven
50KL7469COdd74699OddOdd
51RUSSOdd#VALUE!#VALUE!Odd
52S94COEven944EvenEven
53T5A5MENOdd555OddOdd
54YELOSTNOdd#VALUE!#VALUE!Odd
55YOSEMITOdd#VALUE!#VALUE!Odd
56
Challenge
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D6)
B4,F4,H4B4=FORMULATEXT(B6)
J5,L8J5=FORMULATEXT(J6)
D6:D55D6=REDUCE(A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))
F6:F55F6=--RIGHT(D6#,1)
H6:H55H6=IF(ISODD(+F6#),"Odd","Even")
J6:J55J6=IFERROR(H6#,"Odd")
L9L9=AND(J6#=tblChallenge[Odd/Even])
B6:B55B6=EO([@[Plate Number]])
Dynamic array formulas.
 
Array format, single cell formulas, 2 solutions, using recursive EO(c) and REDUCE
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLMNOPQ
11 solution, using EO(c)
2=MAP(B5:B54,LAMBDA(x,EO(x)))
3↓↓↓2 solution REDUCE, lambda formula
4array↓↓↓=IFERROR(IF(ISODD(--RIGHT(REDUCE(B5:B54,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,""))),1)),"Odd","Even"),"Odd")
5123M57OddOdd
61COU886EvenEvencheck results, both solutions
71D48821OddOdd=AND(F5#=D5#)
81DVT189OddOddTRUE
921W7454EvenEven
1027U17OddOdd
112IGR878EvenEven
122RDZ434EvenEven
132SWQ185OddOdd
143FII697OddOdd
153QOW866EvenEven
163RQC229OddOdd
173RSZ114EvenEven
184FXL296EvenEven
194KUC981OddOdd
204QKB212EvenEven
214QRG241OddOdd
224RVF139OddOdd
235ACY568EvenEven
245IYF234EvenEven
255KFC128EvenEven
265NFD419OddOdd
275RXA984EvenEven
285TGB871OddOdd
295UEZ777OddOdd
305WKT763OddOdd
316LJT722EvenEven
326MVB117OddOdd
336XZR265OddOdd
3471F27UJOddOdd
35745Y4IEvenEven
3676736QEvenEven
377FOB933OddOdd
387JCM371OddOdd
39884PCXEvenEven
408JHC777OddOdd
418RDQ178EvenEven
428TMW513OddOdd
439PMH329OddOdd
449UGW414EvenEven
459UUL349OddOdd
46BEACHOddOdd
47G97C8TEvenEven
48H1R226IEvenEven
49KL7469COddOdd
50RUSSOddOdd
51S94COEvenEven
52T5A5MENOddOdd
53YELOSTNOddOdd
54YOSEMITOddOdd
55
Challenge 2
Cell Formulas
RangeFormula
D2D2=FORMULATEXT(D5)
F4,H7F4=FORMULATEXT(F5)
D5:D54D5=MAP(B5:B54,LAMBDA(x,EO(x)))
F5:F54F5=IFERROR(IF(ISODD(--RIGHT(REDUCE(B5:B54,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,""))),1)),"Odd","Even"),"Odd")
H8H8=AND(F5#=D5#)
Dynamic array formulas.
 
For fun, let's create our own License Plate Generator.
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Concept. Creating 30 random license plates , letters and numbers, 7 chars long.
2Formula trick to give digits close probability as letters have, to be picked up by random engine.(2 sequences of digits 0 to 9) (20 digits+ 26 letters=> sequence dimension=46)
3↓↓↓
4=LET(a,SEQUENCE(46,,65),IF(a>90,RIGHT(a,1),CHAR(a)))=BYROW(L6#,LAMBDA(x,TEXTJOIN(,,x)))
5↓↓↓=RANDARRAY(30,7,1,46,1)=INDEX(B6#,D6#)↓↓↓=MAP(T6#,LAMBDA(x,EO(x)))
6A2361217141935WFLQNS9WFLQNS9Odd
7B438688388D2FHH2HD2FHH2HEven
8C7441917181443G8SQRN7G8SQRN7Odd
9D274292212771D3VLGG1D3VLGGOdd
10E121437247110LN1XGAJLN1XGAJOdd
11F40231234046244B5W40X4B5W40XEven
12G36423117373322065Q17V065Q17VOdd
13H274017515383314QEO2714QEO27Odd
14I1325363312622MY07LFVMY07LFVOdd
15J21420253792UDTY1IBUDTY1IBOdd
16K33303316442630747P8Z4747P8Z4Even
17L331714344647QNCD0D7QNCD0DEven
18M1721223523236QUV9B60QUV9B60Even
19N431016392945277JP33917JP3391Odd
20O721141941325GUNS56EGUNS56EEven
21P163610367428P0J0GD2P0J0GD2Even
22Q6340122443FC4LBD7FC4LBD7Odd
23R2540293538723Y4392GWY4392GWEven
24S31172122126165QUVAZP5QUVAZPOdd
25T45845244435159H9X89O9H9X89OOdd
26U10401828382421J4R22XUJ4R22XUEven
27V312432381446335X62N075X62N07Odd
28W441937453027128S1941L8S1941LOdd
29X932101318359I6JMR9II6JMR9IOdd
30Y143091931337N4ISCM1N4ISCM1Odd
31Z1337249391641M1XI3P5M1XI3P5Odd
321169138321920PIMH6STPIMH6STEven
33222421929453619V6S390SV6S390SEven
3434593932204619I36T0A9I36T0AEven
354182436122422RBD0LXVRBD0LXVEven
365
Lic Plt Gen 1
Cell Formulas
RangeFormula
B4,T4B4=FORMULATEXT(B6)
D5,V5,L5D5=FORMULATEXT(D6)
B6:B51B6=LET(a,SEQUENCE(46,,65),IF(a>90,RIGHT(a,1),CHAR(a)))
D6:J35D6=RANDARRAY(30,7,1,46,1)
L6:R35L6=INDEX(B6#,D6#)
T6:T35T6=BYROW(L6#,LAMBDA(x,TEXTJOIN(,,x)))
V6:V35V6=MAP(T6#,LAMBDA(x,EO(x)))
Dynamic array formulas.
 
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLM
1Single cell formula. Creating n random license plates , letters and numbers, 7 chars long.
2
3n 40
4
5=LET(n,C3,s,SEQUENCE(46,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,46,1)),LAMBDA(x,TEXTJOIN(,,x))))
6↓↓↓
7↓↓↓=MAP(B8#,LAMBDA(x,EO(x)))
8IX6KG32check nEven
9RRBVZB3=ROWS(B8#)Odd
10D2N300Z40Even
11G82NVPIEven
12NDT0OC7Odd
13TZ746G5Odd
14O69H5SROdd
15H4N3O01Odd
16Q8DV0X0Even
17EPT4Y9QOdd
18V2DCW97Odd
19075BN3SOdd
20VL91WO5Odd
21R88Q8S8Even
22J09DA3YOdd
235W0WC6ZEven
24BYJZA99Odd
251152JW7Odd
26RN02CJHEven
277HZJ63FOdd
285YIES07Odd
29GKLX394Even
30K8BTN0CEven
31OP36E8DEven
32NSZZ0OUEven
3315R24C8Even
34BT04CDGEven
351F4F6N1Odd
3651M1607Odd
37W2YJZ5NOdd
380RA3198Even
39CP6MRY8Even
40V10B538Even
417KROR03Odd
427O837O3Odd
43A31Z6SUEven
44692MXHDEven
454P1QMS3Odd
460JW209ROdd
472MX23ICOdd
48
Lic Plt Gen 2
Cell Formulas
RangeFormula
B5B5=FORMULATEXT(B8)
F7,D9F7=FORMULATEXT(F8)
B8:B47B8=LET(n,C3,s,SEQUENCE(46,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,46,1)),LAMBDA(x,TEXTJOIN(,,x))))
F8:F47F8=MAP(B8#,LAMBDA(x,EO(x)))
D10D10=ROWS(B8#)
Dynamic array formulas.
 
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLMNOPQRST
1Single cell formula. Creating n random license plates , letters and numbers, min 5 max 7 chars long. More licenses should have 7 characters.
2This time licenses will contain more digits than previous examples => will use ...s,SEQUENCE(66,,65)…(26 letters and 4 sequences 0-9 => sequence dimention = 26+40=66)
3
4n 32
5
6all 7 chars long strings
7=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))))
8↓↓↓random gen nr. btw 5 and 7
9↓↓↓Trick formula to deliver more of 7 chars than 5 or 6
10↓↓↓=LET(d,RANDARRAY(C4,,5,10,1),IF(d>7,7,d))
11↓↓↓↓↓↓=LEFT(B12#,D12#)5,6,7 charsI11:=MAP(H12#,LAMBDA(x,EO(x)))
12T4GBBO47T4GBBO418BC031←←← =LET(n,C4, s,SEQUENCE(66,,65), q,IF(s>90,RIGHT(s,1),CHAR(s)), b,BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))), d,RANDARRAY(n,,5,10,1), LEFT(b,IF(d>7,7,d)))Odd
13MU88EW47MU88EW420204Even
1498X94M3798X94M31G079L8Even
15K6V90607K6V906010U9Q7XOdd
1695XE64G795XE64GAA142T2Even
17QZF29NL6QZF29N3M6CLEven
18670X4TF7670X4TFQ3190Even
19S8RA7Q35S8RA76DE9QR8Even
20RFHS0FI7RFHS0FI41PZ6MTasks accomplished:Even
21948N6377948N637K5A8FZ8On average, licenses have more digits than letters.Even
22J636P1G6J636P1517X2On average , there are more 7 chars long licenses than 5 or 6 chars.Even
239D1NXP779D1NXP7H900I10Even
2417FV0T8717FV0T89S6463Odd
25H28ZT095H28ZTL12G788Even
2631NO3V9731NO3V9G5U015BOdd
271V23C8871V23C88745293COdd
28H8B69226H8B6921890GEven
298EX34Z168EX34ZP664K9Odd
308493814684938106R77AFOdd
3109XSFC8509XSF17NV3T6Even
32ST82G4E7ST82G4E53MM493Odd
33375DFR77375DFR7T6HZYK2Even
348E06P9568E06P9E511Z6VEven
351I570RS71I570RSVB676REven
364992Y6D74992Y6D0023KAOdd
37292F6A97292F6A95Q5328Even
38NF2851S6NF285152A414Even
3977J1524777J152424JJI62Even
40PE45Z507PE45Z5062X6D6MEven
419W5NDZ179W5NDZ1819Z8Even
42848Q1457848Q1455A88LFEEven
436915907769159070646957Odd
44
Lic Plt Gen 3
Cell Formulas
RangeFormula
B7B7=FORMULATEXT(B12)
D10D10=FORMULATEXT(D12)
F11,R11F11=FORMULATEXT(F12)
B12:B43B12=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))))
D12:D43D12=LET(d,RANDARRAY(C4,,5,10,1),IF(d>7,7,d))
F12:F43F12=LEFT(B12#,D12#)
H12:H43H12=LET(n,C4,s,SEQUENCE(66,,65),q,IF(s>90,RIGHT(s,1),CHAR(s)),b,BYROW(INDEX(q,RANDARRAY(n,7,1,66,1)),LAMBDA(x,TEXTJOIN(,,x))),d,RANDARRAY(n,,5,10,1),LEFT(b,IF(d>7,7,d)))
R12:R43R12=MAP(H12#,LAMBDA(x,EO(x)))
Dynamic array formulas.
 
Found a trick to improve both solutions of Even/Odd main challenge.
Appending a leading 1 to any number, since only last digit is relevant for an even/odd check, will not affect the outcome result.
Why a 1 ? Because if a string has only letters that will be ignored or replaced with "", this 1 will deliver "Odd", and this is exactly what we need.
If we append "1" as leading char to each license plate nr., recursive EO function does not need IF(n=0,"Odd"….anymore.
Also REDUCE/SUBSTITUTE solution delivers only digits, => no need for an IFERROR check anymore.
New recursive EO function in these circumstances
Excel Formula: 
=LAMBDA(a,
    LET(
        c, 1 & a,
        n, LEN(c),
        x, ISODD(RIGHT(c, 1)),
        IF(ISERR(x), EO(LEFT(c, n - 1)), IF(x, "Odd", "Even"))
    )
)
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJKLM
1License Plate ChallengeRule: If last digit of license plate is odd nr. than "Odd", else "Even", if no digits found => "Odd"
2appending 1 trick2nd solution, entire clm vector, 2 steps only
31st solution, new EO=REDUCE(1&A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))
4=EO([@[Plate Number]])↓↓↓=IF(ISODD(+D6#),"Odd","Even")
5Plate NumberOdd/Even↓↓↓↓↓↓
6123M57Odd112357OddNotes:
71COU886Even11886Even - No need to extract right most digit,
81D48821Odd1148821OddISODD(whole nr) will return same results
91DVT189Odd11189Odd - since ISODD is a math op, no need for dbl negative
1021W7454Even1217454Even
1127U17Odd12717Odd=VALUETOTEXT(11)=ISODD(H12)
122IGR878Even12878Even11TRUE
132RDZ434Even12434Even
142SWQ185Odd12185Odd - "+" sign in ISODD(+D6#), only "forces" ISODD to handle arrays
153FII697Odd13697Odd
163QOW866Even13866Even=ISODD(H17:H18)=ISODD(+H17:H18)
173RQC229Odd13229Odd11#VALUE!TRUE
183RSZ114Even13114Even122FALSE
194FXL296Even14296Even
204KUC981Odd14981Odd
214QKB212Even14212Evencheck results, both solutions
224QRG241Odd14241Odd=AND(F6#=tblChallenge[Odd/Even])
234RVF139Odd14139OddTRUE
245ACY568Even15568Even
255IYF234Even15234Even
265KFC128Even15128Even
275NFD419Odd15419Odd
285RXA984Even15984Even
295TGB871Odd15871Odd
305UEZ777Odd15777Odd
315WKT763Odd15763Odd
326LJT722Even16722Even
336MVB117Odd16117Odd
346XZR265Odd16265Odd
3571F27UJOdd17127Odd
36745Y4IEven17454Even
3776736QEven176736Even
387FOB933Odd17933Odd
397JCM371Odd17371Odd
40884PCXEven1884Even
418JHC777Odd18777Odd
428RDQ178Even18178Even
438TMW513Odd18513Odd
449PMH329Odd19329Odd
459UGW414Even19414Even
469UUL349Odd19349Odd
47BEACHOdd1Odd
48G97C8TEven1978Even
49H1R226IEven11226Even
50KL7469COdd17469Odd
51RUSSOdd1Odd
52S94COEven194Even
53T5A5MENOdd155Odd
54YELOSTNOdd1Odd
55YOSEMITOdd1Odd
56
Challenge
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D6)
B4,F4B4=FORMULATEXT(B6)
D6:D55D6=REDUCE(1&A6:A55,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,SUBSTITUTE(v,x,"")))
F6:F55F6=IF(ISODD(+D6#),"Odd","Even")
H11,J11,H22,J16,L16H11=FORMULATEXT(H12)
H12H12=VALUETOTEXT(11)
J12J12=ISODD(H12)
J17J17=ISODD(H17:H18)
L17:L18L17=ISODD(+H17:H18)
H23H23=AND(F6#=tblChallenge[Odd/Even])
B6:B55B6=EO([@[Plate Number]])
Dynamic array formulas.
 
3rd solution, and still unconventional (without using MID)
Excel-Challenge-License-Plates.xlsx
ABCDEFGHIJ
1License Plate Challenge
2
33rd solution
4=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&A6,CHAR(SEQUENCE(26,,65))))),"Odd","Even")
5Plate NumberOdd/Even
6123M57Odd
71COU886EvenIf strings with lowercase values, we can use UPPER
81D48821Odd
91DVT189Odd=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&UPPER(D11),CHAR(SEQUENCE(26,,65))))),"Odd","Even")
1021W7454Even↓↓↓
1127U17OddzAOdd
122IGR878Evenxa34dEven
132RDZ434EvenDsTrOdd
142SWQ185Oddz357wP300fGEven
153FII697Odd2uyfoTYFIIYTFEven
163QOW866Even2VnM2223lLkJHOdd
173RQC229Odd
183RSZ114Even
194FXL296Even
204KUC981Odd
214QKB212Even
224QRG241Odd
234RVF139Odd
245ACY568Even
255IYF234Even
265KFC128Even
275NFD419Odd
285RXA984Even
295TGB871Odd
305UEZ777Odd
315WKT763Odd
326LJT722Even
336MVB117Odd
346XZR265Odd
3571F27UJOdd
36745Y4IEven
3776736QEven
387FOB933Odd
397JCM371Odd
40884PCXEven
418JHC777Odd
428RDQ178Even
438TMW513Odd
449PMH329Odd
459UGW414Even
469UUL349Odd
47BEACHOdd
48G97C8TEven
49H1R226IEven
50KL7469COdd
51RUSSOdd
52S94COEven
53T5A5MENOdd
54YELOSTNOdd
55YOSEMITOdd
56
Challenge
Cell Formulas
RangeFormula
B4B4=FORMULATEXT(B6)
D9D9=FORMULATEXT(F11)
F11:F16F11=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&UPPER(D11),CHAR(SEQUENCE(26,,65))))),"Odd","Even")
B6:B55B6=IF(ISODD(TEXTJOIN(,,TEXTSPLIT(1&A6,CHAR(SEQUENCE(26,,65))))),"Odd","Even")
 
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