SOLVED

Contributor

# 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

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

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

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

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?

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

I just checked it does create the same problem at column EL

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

Perhaps:
=IFERROR(MID(REPT("111000",ROUNDUP(COLUMNS(Recipe)/6,0)),COLUMNS(\$B3:B3)-1,1),0)

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

This is not a dynamic formula.
I need one to spill result

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

I'm working off XL2016 and don't have dynamic array excel. Try changing this part
COLUMNS(\$B3:B3)

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

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

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.

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

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

# Re: How to generate a sequence with 3 consecutives 1's and 3 consecutives 0's based on a table?

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.