Forum Discussion
How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?
I've already got a formula, it's almost efficient
Needs minor correction.
I've attached a sample file. It's self explanatory.
PeterBartholomew1 SergeiBaklan
I would recommend sticking with integers for this type of calculation in order to avoid rounding error problems altogether. In particular, the integer divide function QUOTIENT can be valuable when you want to group values.
= LET( k, SEQUENCE(1,COLUMNS(Recipe),2), MOD(QUOTIENT(k,3),2) )Normally 'k' should be a zero-based index but here you want a two cell offset with only one opening zero.
9 Replies
I would recommend sticking with integers for this type of calculation in order to avoid rounding error problems altogether. In particular, the integer divide function QUOTIENT can be valuable when you want to group values.
= LET( k, SEQUENCE(1,COLUMNS(Recipe),2), MOD(QUOTIENT(k,3),2) )Normally 'k' should be a zero-based index but here you want a two cell offset with only one opening zero.
- Thank you sir.
I didn't understand the zero-based index thing.
Although I'm okay with the first cell not being a zero,
If it directly starts with consecutive 1s and 0s. That works as well- If you were to use Power Query, say, you would see an option to create an index column. Normally one would think of numbering the records starting at 1, {1;2;3;4;...} but, for some calculations, starting at 0 is better {0;1;2;3;...}.
To start with consecutive zeros, your sequence formula should start of 0 rather than 2.
Nishkarsh31 Suspected it to a rounding issue. Your formula included a factor 0.33. Changed it to ⅓. Then it seems to work.
- I just checked it does create the same problem at column EL
- Perhaps:
=IFERROR(MID(REPT("111000",ROUNDUP(COLUMNS(Recipe)/6,0)),COLUMNS($B3:B3)-1,1),0)
- It did solve it. However 1/3 is also calculated as 0.333333333333333
Won't it also create problem, after a particular no. of columns?
