Jon wants to generate 6-letter sequences using only the letters B and P.
So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE.
Formulas used for one word:
=SUBSTITUTE(SUBSTITUTE(BASE(RANDBETWEEN(0,63),2,6),”0″,”B”),”1″,”P”)
The red box suggested a shorter formula of:
=CONCAT(CHAR(66+14*RANDARRAY(6,1,0,1,TRUE)))
Formula for all 64 words, in sequence
=SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),”0″,”B”),”1″,”P”)
Formula for those 64 words sorted randomly
=SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),”0″,”B”),”1″,”P”),RANDARRAY(64))
If you have the number of letters in A3, then generate all words in sequence
=SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),”0″,”B”),”1″,”P”)
Sort those with:
=SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(2^$A$3,1,0),2,$A$3),”0″,”B”),”1″,”P”),RANDARRAY(2^$A$3))