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:
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.
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.
There are 10!/(8!*2!) = 45 ways of arranging your True/False requirements
I personally didn't have anything better to do with my time so I wrote out all possible combinations in 45 columns.
The concept with this methodology is to randomly write out one of the 45 columns every 10 rows. One of the problems here is that using random in a formula does not mean you will be able to use the same random value in the next row of the formula.
In order to make a random result accessible by multiple formula calculations one can spit out the results in a helper column. For this solution we will be randomly selecting from 45 possible columns, so in the first column the following formula is used and copied down. The number of rows will be equal to the number of 10 groupings you will use.
Start in A1 and copy down
=RANDBETWEEN(1,45)
For demonstration purposes the next column is to generate integers starting at 1 and increasing by 1 after every 10 rows. For the demonstration it would need to be copied down a number of rows equal to the number of results needed (10 * number of groups of 10). Ultimately this formula can be embedded in the final formula.
Start in B1 and copy down
=INT((ROW(A1)-1)/10)+1
For demonstration purposes the next column is to generate integers starting at 1 and increasing by 1 row but resetting to 1 after the 10th row. For the demonstration it would need to be copied down a number of rows equal to the number of results needed (10 * number of groups of 10). Ultimately this formula can be embedded in the final formula.
Start in C1 and copy down
=MOD(ROW(A1)-1,10)+1
So now there is a way of indexing the column you need and what row of that column you need.
In the next column the index function is used (twice) to find out what column and row to look in from the list of all possible combination. In this demo, the list of all possible combination is written out from F1:AX10.
First we start by indexing which random column to use. Since the random numbers are written in column A starting in row 1 I used the following formula:
=INDEX(A:A,B1)
To get the row reference I used the following formula:
=C1
I then took those two formulas and combined them to pull data from the possibility table as follows:
Start in D1
=INDEX($F$1:$AX$10,C1,INDEX(A:A,B1))
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))
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 array formula in A1:A10 is
=INDEX($C$1:$L$10,,1+MOD(RANDBETWEEN(1,100),10))
column B is just an indexing column containing the formula
=1+MOD(ROW()-1,10)
which, coupled with the conditional formatting in column A illustrates that the positions of the FALSE values are different in each 10-row sequence.
(you will notice that the random numbers generated in columns I and J happen to be the same so, if this is a concern, you could extend the 'helper range' beyond 10 columns in order to augment randomness)