I need to add a column to my spread sheet that generates two "false" at random intervals every ten frames.
So for example rows 1 though 10 could read:
- true
- true
- true
- False
- true
- false
- true
- true
- true
- true
and then repeat that for rows 11 through 20, but the false are randomly put in different places. etc. I want write a formula that does this for me.
CodePudding user response：
With Office 365:
In first cell you want the list to be created put:
=LET(rws,1000,arr,RANDARRAY(10,rws/10),seq,SEQUENCE(rws,,0),INDEX(MAKEARRAY(10,rws/10,LAMBDA(i,j,INDEX(BYCOL(arr,LAMBDA(v,MATCH(SMALL(v,i),v,0))),1,j)<9)),MOD(seq,10) 1,INT(seq/10) 1))
Change the 1000 to the number of rows desired.
If one does not have Office 365 then put this in the second row of a column and copy it down.
=IF(COUNTIF(INDEX(A:A,MIN(ROW($ZZ1)-MOD(ROW($ZZ1)-1,10) 1,ROW()-1)):INDEX(A:A,ROW()-1),FALSE)>=2,TRUE,IF(COUNTIF(INDEX(A:A,MIN(ROW($ZZ1)-MOD(ROW($ZZ1)-1,10) 1,ROW()-1)):INDEX(A:A,ROW()-1),TRUE)>=8,FALSE,RANDBETWEEN(0,9)<8))
Be aware:
Each cell is randomly chosen and as such FALSE will appear in the last of the 10 more often than truly random. One can play with the RANDBETWEEN(0,9)<8 to maybe make that more random.
CodePudding user response：
It's not clear which version of Excel you're using so this approach will work for all versions:
the starting point is C12:L13, where the formula in row 12 is
=RANDBETWEEN(1,5)
and the formula in row 13 is
=RANDBETWEEN(6,10)
These results determine the positions of the FALSE values in the range starting with cell C1 where the formula is
=NOT(OR(ROW()=C$12,ROW()=C$13))
The 
Tidying it up
We can't eliminate the random number column as we need something quasi static for the formulas to refer to. The reason I say quasi static, random is a volatile function which means it will recalculate every time the sheet recalculates. However, we can place the formulas from B and C into D. This results in the formula in D looking like:
=INDEX($F$1:$AX$10,MOD(ROW(A1)-1,10) 1,INDEX(A:A,INT((ROW(A1)-1)/10) 1))
