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.

@Peter Bartholomew @Sergei Baklan 

9 Replies

@Nishkarsh31 Suspected it to a rounding issue. Your formula included a factor 0.33. Changed it to ⅓. Then it seems to work.

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?
I just checked it does create the same problem at column EL
This is not a dynamic formula.
I need one to spill result
I'm working off XL2016 and don't have dynamic array excel. Try changing this part

using the sequence function to generate a sequence of: 1 - Columns(Recipe)
best response confirmed by Nishkarsh31 (Contributor)


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.