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.
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)
=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)