Forum Discussion
Solved
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.
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)- 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
COLUMNS($B3:B3)
using the sequence function to generate a sequence of: 1 - Columns(Recipe)
