SEQUENCES is a powerful and versatile multi-SEQUENCE solution providing full control over "start" of each sequence as well as each of the "counts" and "steps" and having other useful features such as stacking and sorting
The overall function parameters are similar to the native SEQUENCE function, with the following differences:
1) "rows" and "columns" have been replaced with "counts"
2) "start" and "breaks" allow for full control over the start of each sequence
3) "step" has been enhanced to "steps" to allow full control for the step of each sequence
4) "stack" allows for changing the output from linear to stacked
5) "sort" allows for sorting even in the stacked format
Thus, SEQUENCES allows for creating multiple sequences at once with the ability to control all of their properties independently of one another. SEQUENCES also allows for incorporating blanks wherever needed as well as filtering out any zero counts (see description below)
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
SEQUENCES takes six parameters, one required and five optional, as follows:
A) The first parameter, required, specifies the total number(s) of sequences as well as how many number(s) needed for each sequence and can be one or more integers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the total counts of numbers specifies the number of needed sequences (e.g. 3 or {1} indicates that one sequence is needed while {4,1} indicates two sequences)
Note that each of the numbers indicates the total count of numbers for each sequence (e.g. {5,3} means that the first sequence has five numbers and the second has three)
Note that any zero(s) in this parameter are filtered out implying that the sequence(s) corresponding to the position(s) of the zero(s) will be eliminated from the output; note, however, that {4,5} will not necessarily result in the same two sequences as {4,0,5} due to possible differences between "breaks" and/or "steps" (see items C and D below)
Note that including negative number(s) in this parameter will result in sequence(s) of blanks with count(s) corresponding to the absolute value(s) of the negative number(s)
Note that entering numbers in column format (i.e. with semicolons) (e.g. {3;6;2} will result in an output which is equal to the transposed version of row format (i.e. counts with commas)
Note that the numbers entered in this parameter can be either one-dimensional arrays (either in row format or column format) or two-dimensional as the function linearizes the "counts"
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
B) The second parameter, optional, specifies the "start" of the first specified sequence and can be any single real number
Note that if this parameter is left omitted, the first sequence will have the default start of 1 and any subsequent sequence will start 1 higher (as long as the "breaks" parameter is left omitted; see item C below)
Note that if more than one number is entered, the function will crash to an #N/A
Note that if anything other than a number is entered, the function will crash to an #N/A
C) The third parameter, optional, specifies the numerical difference(s) between the start of the sequences specified in the "counts" parameter and can be one or more real numbers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that if this parameter is left omitted, the default break of one will be automatically applied to all sequences following the first one (i.e. the start of each sequence will be 1 higher than the previous)
Note that if a single number is entered, that break will be applied to all sequences following the first one
Note, that if more than one number is entered, they will be applied to the numbers in "counts" respectively; if the numbers of "breaks" is fewer than that of "counts", the default break of 1 will be automatically assigned to the remaining numbers of "counts"
Note that if there are any additional numbers in "breaks", past the last point of correspondence to "counts", they will be ignored by the function; thus, if more numbers are later added to "counts", the additional numbers in "breaks" will be automatically applied to those added numbers
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
D) The fourth parameter, optional, specifies the "steps" for the specified sequence(s) and can be one or more real numbers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that if this parameter is left omitted, the default step of 1 will be applied to all sequences
Note that if a single number is entered, this number will be applied as step to all sequences
Note, that if more than one number is entered, they will be applied to the numbers in "counts" respectively; if the numbers of "steps" is fewer than that of "counts", the default step of 1 will be automatically assigned to the remaining numbers of "counts"
Note that if there are any additional numbers in "steps", past the last point of correspondence to "counts", they will be ignored by the function; thus, if more numbers are later added to "counts", the additional numbers in "steps" will be automatically applied to those added numbers
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
E) The fifth parameter, optional, specifies the output format of the function and takes two general arguments:
0 or omitted or FALSE, for linear output
1 or TRUE or any number other than 0, for stacked output
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to an #N/A
Note that if anything other than the specified allowed arguments is entered, the function will crash to an #N/A
F) The sixth parameter, optional, specifies whether the function output is sorted or not as well as the direction of sort and takes the same two arguments of the native SORT function:
1, for ascending sort
-1, for descending sort
Note that if the function output is linear, the entire elements of the output will be subjected to the specified sort, while if the function output is stacked, each column (or row, in case of column "counts") will be subjected to the specified sort independently
This function is mostly based on the following two threads:
Special thanks to Stephen (@StephenCrump) and @djclements for making critical elements of this function possible
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
Note that in the function code, the last IF is not strictly needed for the core functionality; it is there just to ensure the correct arguments are entered in all parameters. Thus, the function will work perfectly fine with the last variable being "h".
SEQUENCES
ISNUMBERS (Returns TRUE if ALL elements in the reference are numbers and FALSE otherwise)
IO (shortened form of ISOMITTED)
The overall function parameters are similar to the native SEQUENCE function, with the following differences:
1) "rows" and "columns" have been replaced with "counts"
2) "start" and "breaks" allow for full control over the start of each sequence
3) "step" has been enhanced to "steps" to allow full control for the step of each sequence
4) "stack" allows for changing the output from linear to stacked
5) "sort" allows for sorting even in the stacked format
Thus, SEQUENCES allows for creating multiple sequences at once with the ability to control all of their properties independently of one another. SEQUENCES also allows for incorporating blanks wherever needed as well as filtering out any zero counts (see description below)
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
SEQUENCES takes six parameters, one required and five optional, as follows:
A) The first parameter, required, specifies the total number(s) of sequences as well as how many number(s) needed for each sequence and can be one or more integers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the total counts of numbers specifies the number of needed sequences (e.g. 3 or {1} indicates that one sequence is needed while {4,1} indicates two sequences)
Note that each of the numbers indicates the total count of numbers for each sequence (e.g. {5,3} means that the first sequence has five numbers and the second has three)
Note that any zero(s) in this parameter are filtered out implying that the sequence(s) corresponding to the position(s) of the zero(s) will be eliminated from the output; note, however, that {4,5} will not necessarily result in the same two sequences as {4,0,5} due to possible differences between "breaks" and/or "steps" (see items C and D below)
Note that including negative number(s) in this parameter will result in sequence(s) of blanks with count(s) corresponding to the absolute value(s) of the negative number(s)
Note that entering numbers in column format (i.e. with semicolons) (e.g. {3;6;2} will result in an output which is equal to the transposed version of row format (i.e. counts with commas)
Note that the numbers entered in this parameter can be either one-dimensional arrays (either in row format or column format) or two-dimensional as the function linearizes the "counts"
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
B) The second parameter, optional, specifies the "start" of the first specified sequence and can be any single real number
Note that if this parameter is left omitted, the first sequence will have the default start of 1 and any subsequent sequence will start 1 higher (as long as the "breaks" parameter is left omitted; see item C below)
Note that if more than one number is entered, the function will crash to an #N/A
Note that if anything other than a number is entered, the function will crash to an #N/A
C) The third parameter, optional, specifies the numerical difference(s) between the start of the sequences specified in the "counts" parameter and can be one or more real numbers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that if this parameter is left omitted, the default break of one will be automatically applied to all sequences following the first one (i.e. the start of each sequence will be 1 higher than the previous)
Note that if a single number is entered, that break will be applied to all sequences following the first one
Note, that if more than one number is entered, they will be applied to the numbers in "counts" respectively; if the numbers of "breaks" is fewer than that of "counts", the default break of 1 will be automatically assigned to the remaining numbers of "counts"
Note that if there are any additional numbers in "breaks", past the last point of correspondence to "counts", they will be ignored by the function; thus, if more numbers are later added to "counts", the additional numbers in "breaks" will be automatically applied to those added numbers
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
D) The fourth parameter, optional, specifies the "steps" for the specified sequence(s) and can be one or more real numbers
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that if this parameter is left omitted, the default step of 1 will be applied to all sequences
Note that if a single number is entered, this number will be applied as step to all sequences
Note, that if more than one number is entered, they will be applied to the numbers in "counts" respectively; if the numbers of "steps" is fewer than that of "counts", the default step of 1 will be automatically assigned to the remaining numbers of "counts"
Note that if there are any additional numbers in "steps", past the last point of correspondence to "counts", they will be ignored by the function; thus, if more numbers are later added to "counts", the additional numbers in "steps" will be automatically applied to those added numbers
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
E) The fifth parameter, optional, specifies the output format of the function and takes two general arguments:
0 or omitted or FALSE, for linear output
1 or TRUE or any number other than 0, for stacked output
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to an #N/A
Note that if anything other than the specified allowed arguments is entered, the function will crash to an #N/A
F) The sixth parameter, optional, specifies whether the function output is sorted or not as well as the direction of sort and takes the same two arguments of the native SORT function:
1, for ascending sort
-1, for descending sort
Note that if the function output is linear, the entire elements of the output will be subjected to the specified sort, while if the function output is stacked, each column (or row, in case of column "counts") will be subjected to the specified sort independently
This function is mostly based on the following two threads:
Hello,
Here is a rather complex sequence question
Suppose I have the following partial LET function:
The numbers in b indicate the count of numbers in each piece of the final sequence. The first piece starts with 1, and each piece after the first number is incremented by multiples of c starting at 1c for the second number of b, then 2c for the third number of b, and so on.
For the example above, the sequence starts with 1 and goes up for the indicated count in b, in this case 2, so the first piece of the sequence will be...
Here is a rather complex sequence question
Suppose I have the following partial LET function:
Excel Formula:
=LET(
b, {2,1,3,4,2},
c, 12,
operation
)The numbers in b indicate the count of numbers in each piece of the final sequence. The first piece starts with 1, and each piece after the first number is incremented by multiples of c starting at 1c for the second number of b, then 2c for the third number of b, and so on.
For the example above, the sequence starts with 1 and goes up for the indicated count in b, in this case 2, so the first piece of the sequence will be...
Hello,
This question is related to my previous question below:
www.mrexcel.com
I took Stephen's amazing solution and spiced it up to make my LAMBDA more feature-rich, and it works really well (below)
:
This question is related to my previous question below
:
How to generate the specified sequence of numbers based on incrementing portions of the sequence
Hello, Here is a rather complex sequence question 😀 Suppose I have the following partial LET function: =LET( b, {2,1,3,4,2}, c, 12, operation ) The numbers in b indicate the count of numbers in each piece of the final sequence. The first piece starts with 1, and each piece after the...
I took Stephen's amazing solution and spiced it up to make my LAMBDA more feature-rich, and it works really well (below)
:
Excel Formula:
=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,1,
b,TOROW(counts),
c,break,
g,IF(ISOMITTED(step),1,step),
m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1)))...Special thanks to Stephen (@StephenCrump) and @djclements for making critical elements of this function possible
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
Note that in the function code, the last IF is not strictly needed for the core functionality; it is there just to ensure the correct arguments are entered in all parameters. Thus, the function will work perfectly fine with the last variable being "h".
SEQUENCES
Excel Formula:
=LAMBDA(counts,[start],[breaks],[steps],[stack],[sort],
LET(
v,TOROW(counts),
b,ABS(v),
c,TOROW(IF(IO(breaks),1,breaks)),
a,COUNT(b),
j,COUNT(c),
u,TOROW(steps),
e,COUNT(u),
g,IF(OR(IO(steps),e>1),1,INDEX(u,1)),
q,ABS(IF(g,g,1)),
o,(
IF(v<0,"",IF(ISERROR(b+u),1,u))-1)*
SEQUENCE(MAX(b),,0,IF(e>1,1,0)
),
l,IF(IO(stack),0,INDEX(stack,1)),
n,IF(IO(sort),0,INDEX(sort,1)),
m,SEQUENCE(MAX(b),,,q),
k,IFS(
m<=(b*q),
IF(g<=0,SEQUENCE(MAX(b),,,g),m)+
IF(
j=1,
SEQUENCE(,COUNT(b),0,INDEX(c,1)),
LET(
r,SCAN(0,c,LAMBDA(y,x,y+x)),
t,a-COUNT(r),
HSTACK(0,r,SEQUENCE(,IF(t>0,t,1),MAX(r)+1,1))
)
)
),
p,CHOOSECOLS(k+o,FILTER(SEQUENCE(,a),b<>0)),
d,IF(
l,
p,
TOROW(IF(ISERROR(p),IF(ERROR.TYPE(p)=3,"",p),p),2,1)
),
i,d+(IF(IO(start),0,start-1)),
w,IF(
n,
IF(
l,
IFNA(
WRAPCOLS(
SORTBY(TOCOL(i),
TOCOL(IF({1},SEQUENCE(,COLUMNS(i)),i)),1,
TOCOL(ISNA(i)),1,
TOCOL(i),n
),
ROWS(i)
),
""
),
IF(
PRODUCT((counts>=0)*1),
SORT(i,,n,1),
IFNA(
TOROW(LET(
za,TRANSPOSE(i),
zb,ROWS(za),
zc,SEQUENCE(zb),
zd,ISERROR(za),
ze,NOT(zd),
zf,TOCOL(za,2,1),
zg,TOCOL(IFS(ze,1),2,1),
zh,SORTBY(EXPAND(SORTBY(zf,zg,1,zf,n),zb*1),
VSTACK(zg,TOCOL(IFS(zd,1),2,1)),1,
VSTACK(TOCOL(IFS(ze,zc),2,1),TOCOL(IFS(zd,zc),2,1)),1
),
WRAPCOLS(zh,zb))
),
""
)
)
),
IFNA(i,"")
),
f,IF(ISERROR(w),IF(ERROR.TYPE(w)=3,"",w),w),
h,IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)),
IF(
OR(IO(breaks),ISNUMBERS(breaks))*
(COUNT(start)<=1)*
OR(ISNUMBER(start),IO(start))*
ISNUMBERS(steps,TRUE)*
(COUNT(l)<=1)*
ISNUMBER(l)*
ISNUMBERS(counts,TRUE)*
ISNUMBERS(sort,TRUE)*
OR(IO(sort),sort=1,sort=-1)*
OR(IO(stack),COUNT(stack)=1),
h,
NA()
)
)
)ISNUMBERS (Returns TRUE if ALL elements in the reference are numbers and FALSE otherwise)
Excel Formula:
=LAMBDA(reference,[if_omitted],
IF(
IO(reference),
IF(IO(if_omitted),FALSE,if_omitted),
IF(PRODUCT(ISNUMBER(reference)*1),TRUE,FALSE)
)
)IO (shortened form of ISOMITTED)
Excel Formula:
=LAMBDA(parameter,
ISOMITTED(parameter)
)| SEQUENCES.xlsx | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 1 | ||||||||||||||||||||||||
| 2 | #CALC! | |||||||||||||||||||||||
| 3 | ||||||||||||||||||||||||
| 4 | 1 | 2 | 2 | 3 | 4 | 5 | 4 | 5 | 6 | 7 | 8 | |||||||||||||
| 5 | ||||||||||||||||||||||||
| 6 | 1 | 2 | 3 | 4 | 5 | 1 | 2 | 2 | 3 | 4 | 3 | 4 | 4 | 5 | 6 | 7 | ||||||||
| 7 | 2 | 3 | 4 | 6 | 1 | 2 | 3 | 4 | 4 | 5 | 6 | 7 | ||||||||||||
| 8 | 4 | 5 | 7 | 1 | 2 | 3 | 4 | 4 | 5 | 6 | 7 | |||||||||||||
| 9 | 5 | 8 | 7 | 6 | 5 | 4 | 4 | 3 | 2 | 1 | ||||||||||||||
| 10 | 1 | 2 | 3 | 4 | 4 | 5 | 6 | 7 | ||||||||||||||||
| 11 | 1 | 1 | 2 | 7 | 6 | 5 | 4 | 4 | 3 | 2 | 1 | |||||||||||||
| 12 | 2 | 2 | 3 | 4 | 5 | |||||||||||||||||||
| 13 | 2 | 3 | 4 | 5 | ||||||||||||||||||||
| 14 | 3 | 4 | ||||||||||||||||||||||
| 15 | 4 | 5 | 6 | 7 | 8 | |||||||||||||||||||
| 16 | 5 | |||||||||||||||||||||||
| 17 | 3 | |||||||||||||||||||||||
| 18 | 4 | |||||||||||||||||||||||
| 19 | 5 | |||||||||||||||||||||||
| 20 | 4 | |||||||||||||||||||||||
| 21 | 5 | |||||||||||||||||||||||
| 22 | 6 | |||||||||||||||||||||||
| 23 | 7 | |||||||||||||||||||||||
| 24 | 8 | |||||||||||||||||||||||
| 25 | ||||||||||||||||||||||||
| 26 | start: | omitted | start: | 2.2 | start: | -3.5 | ||||||||||||||||||
| 27 | 1 | 2 | 3 | 4 | 5 | 6 | 2.2 | 3.2 | 4.2 | 5.2 | 6.2 | 7.2 | -3.5 | -2.5 | -1.5 | -0.5 | 0.5 | 1.5 | ||||||
| 28 | 2 | 3 | 4 | 5 | 6 | 7 | 3.2 | 4.2 | 5.2 | 6.2 | 7.2 | 8.2 | -2.5 | -1.5 | -0.5 | 0.5 | 1.5 | 2.5 | ||||||
| 29 | 3 | 4 | 6 | 7 | 8 | 4.2 | 5.2 | 7.2 | 8.2 | 9.2 | -1.5 | -0.5 | 1.5 | 2.5 | 3.5 | |||||||||
| 30 | 5 | 7 | 9 | 6.2 | 8.2 | 10.2 | 0.5 | 2.5 | 4.5 | |||||||||||||||
| 31 | 6 | 10 | 7.2 | 11.2 | 1.5 | 5.5 | ||||||||||||||||||
| 32 | 7 | 8.2 | 2.5 | |||||||||||||||||||||
| 33 | 8 | 9.2 | 3.5 | |||||||||||||||||||||
| 34 | ||||||||||||||||||||||||
| 35 | breaks: | 1.73 | breaks: | {1.73,-2} | breaks: | {1.73,-2,0,4} | ||||||||||||||||||
| 36 | 1 | 2.73 | 4.46 | 6.19 | 7.92 | 9.65 | 1 | 2.73 | 0.73 | 3.73 | 4.73 | 5.73 | 1 | 2.73 | 0.73 | 0.73 | 4.73 | 5.73 | ||||||
| 37 | 2 | 3.73 | 5.46 | 7.19 | 8.92 | 10.65 | 2 | 3.73 | 1.73 | 4.73 | 5.73 | 6.73 | 2 | 3.73 | 1.73 | 1.73 | 5.73 | 6.73 | ||||||
| 38 | 3 | 4.73 | 8.19 | 9.92 | 11.65 | 3 | 4.73 | 5.73 | 6.73 | 7.73 | 3 | 4.73 | 2.73 | 6.73 | 7.73 | |||||||||
| 39 | 5.73 | 9.19 | 12.65 | 5.73 | 6.73 | 8.73 | 5.73 | 3.73 | 8.73 | |||||||||||||||
| 40 | 6.73 | 13.65 | 6.73 | 9.73 | 6.73 | 9.73 | ||||||||||||||||||
| 41 | 7.73 | 7.73 | 7.73 | |||||||||||||||||||||
| 42 | 8.73 | 8.73 | 8.73 | |||||||||||||||||||||
| 43 | ||||||||||||||||||||||||
| 44 | steps: | 0.75 | steps: | {0.75,2.33,1.5} | steps: | {0.75,2.33,1.5,1,-2.5,0,3,4} | ||||||||||||||||||
| 45 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | ||||||
| 46 | 1.75 | 2.75 | 4.75 | 5.75 | 6.75 | 1.75 | 4.33 | 4.5 | 5 | 6 | 7 | 1.75 | 4.33 | 4.5 | 5 | 2.5 | 6 | |||||||
| 47 | 3.5 | 5.5 | 7.5 | 2.5 | 6.66 | 6 | 7 | 8 | 2.5 | 6.66 | 6 | 0 | 6 | |||||||||||
| 48 | 4.25 | 8.25 | 8.99 | 7 | 9 | 8.99 | 7 | 6 | ||||||||||||||||
| 49 | 5 | 11.32 | 10 | 11.32 | 6 | |||||||||||||||||||
| 50 | 5.75 | 13.65 | 13.65 | |||||||||||||||||||||
| 51 | 15.98 | 15.98 | ||||||||||||||||||||||
| 52 | ||||||||||||||||||||||||
| 53 | sort: | -1 | counts: | {3,-7,2,4,3,5} | counts: | {3,0,2,4,3,5} | ||||||||||||||||||
| 54 | 3 | 8 | 4 | 7 | 7 | 10 | 1 | 3 | 4 | 5 | 6 | 1 | 3 | 4 | 5 | 6 | ||||||||
| 55 | 2 | 7 | 3 | 6 | 6 | 9 | 2 | 4 | 5 | 6 | 7 | 2 | 4 | 5 | 6 | 7 | ||||||||
| 56 | 1 | 6 | 5 | 5 | 8 | 3 | 6 | 7 | 8 | 3 | 6 | 7 | 8 | |||||||||||
| 57 | 5 | 4 | 7 | 7 | 9 | 7 | 9 | |||||||||||||||||
| 58 | 4 | 6 | 10 | 10 | ||||||||||||||||||||
| 59 | 3 | |||||||||||||||||||||||
| 60 | 2 | |||||||||||||||||||||||
| 61 | ||||||||||||||||||||||||
| 62 | ||||||||||||||||||||||||
| 63 | #CALC! | #CALC! | 3 | 2 | ||||||||||||||||||||
| 64 | 4 | 3 | ||||||||||||||||||||||
| 65 | ||||||||||||||||||||||||
| 66 | ||||||||||||||||||||||||
| 67 | ||||||||||||||||||||||||
| 68 | 4.5 | 1.5 | 3.5 | 5 | 5 | 3 | 4.5 | 7 | 1 | 2 | 3 | |||||||||||||
| 69 | 7 | 6.5 | 0.5 | 5 | 4 | 7 | 1.5 | 6.5 | 11.5 | 16.5 | 21.5 | |||||||||||||
| 70 | 11.5 | -2.5 | 5 | 3 | 11 | 3.5 | 0.5 | -2.5 | -5.5 | |||||||||||||||
| 71 | 16.5 | -5.5 | 5 | 15 | 5 | 5 | 5 | 5 | ||||||||||||||||
| 72 | 21.5 | 19 | 5 | 4 | 3 | |||||||||||||||||||
| 73 | 3 | 7 | 11 | 15 | 19 | |||||||||||||||||||
| 74 | ||||||||||||||||||||||||
| 75 | 3 | 3 | 1 | 2 | ||||||||||||||||||||
| 76 | ||||||||||||||||||||||||
| 77 | ||||||||||||||||||||||||
| 78 | 16 | 17 | 18 | 19 | ||||||||||||||||||||
| 79 | 19 | 20 | 21 | 22 | 23 | |||||||||||||||||||
| 80 | 22 | 23 | 24 | 25 | 26 | 27 | ||||||||||||||||||
| 81 | ||||||||||||||||||||||||
SEQUENCES | ||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(counts,[start],[breaks],[steps],[stack],[sort],LET(v,TOROW(counts),b,ABS(v),c,TOROW(IF(IO(breaks),1,breaks)),a,COUNT(b),j,COUNT(c),u,TOROW(steps),e,COUNT(u),g,IF(OR(IO(steps),e>1),1,INDEX(u,1)),q,ABS(IF(g,g,1)),o,(IF(v<0,"",IF(ISERROR(b+u),1,u))-1)*SEQUENCE(MAX(b),,0,IF(e>1,1,0)),l,IF(IO(stack),0,INDEX(stack,1)),n,IF(IO(sort),0,INDEX(sort,1)),m,SEQUENCE(MAX(b),,,q),k,IFS(m<=(b*q),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+IF(j=1,SEQUENCE(,COUNT(b),0,INDEX(c,1)),LET(r,SCAN(0,c,LAMBDA(y,x,y+x)),t,a-COUNT(r),HSTACK(0,r,SEQUENCE(,IF(t>0,t,1),MAX(r)+1,1))))),p,CHOOSECOLS(k+o,FILTER(SEQUENCE(,a),b<>0)),d,IF(l,p,TOROW(IF(ISERROR(p),IF(ERROR.TYPE(p)=3,"",p),p),2,1)),i,d+(IF(IO(start),0,start-1)),w,IF(n,IF(l,IFNA(WRAPCOLS(SORTBY(TOCOL(i),TOCOL(IF({1},SEQUENCE(,COLUMNS(i)),i)),1,TOCOL(ISNA(i)),1,TOCOL(i),n),ROWS(i)),""),IF(PRODUCT((counts>=0)*1),SORT(i,,n,1),IFNA(TOROW(LET(za,TRANSPOSE(i),zb,ROWS(za),zc,SEQUENCE(zb),zd,ISERROR(za),ze,NOT(zd),zf,TOCOL(za,2,1),zg,TOCOL(IFS(ze,1),2,1),zh,SORTBY(EXPAND(SORTBY(zf,zg,1,zf,n),zb*1),VSTACK(zg,TOCOL(IFS(zd,1),2,1)),1,VSTACK(TOCOL(IFS(ze,zc),2,1),TOCOL(IFS(zd,zc),2,1)),1),WRAPCOLS(zh,zb))),""))),IFNA(i,"")),f,IF(ISERROR(w),IF(ERROR.TYPE(w)=3,"",w),w),h,IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)),IF(OR(IO(breaks),ISNUMBERS(breaks))*(COUNT(start)<=1)*OR(ISNUMBER(start),IO(start))*ISNUMBERS(steps,TRUE)*(COUNT(l)<=1)*ISNUMBER(l)*ISNUMBERS(counts,TRUE)*ISNUMBERS(sort,TRUE)*OR(IO(sort),sort=1,sort=-1)*OR(IO(stack),COUNT(stack)=1),h,NA()))) |
| B4:O4 | B4 | =SEQUENCES({2,4,-3,1,4}) |
| B6:F9 | B6 | =SEQUENCES({2,4,3,1,4},,,,1) |
| B11:B24 | B11 | =SEQUENCES({2;4;3;1;4}) |
| D11:G15 | D11 | =SEQUENCES({2;4;3;1;4},,,,1) |
| I6:S6 | I6 | =SEQUENCES({2,3,2,4}) |
| I7:S7 | I7 | =SEQUENCES({2,-3,2,4}) |
| I8:S8 | I8 | =SEQUENCES({2,-3,2,4},,,,,1) |
| I9:S9 | I9 | =SEQUENCES({2,-3,2,4},,,,,-1) |
| I10:P10 | I10 | =SEQUENCES({2,0,2,4},,,,,1) |
| I11:P11 | I11 | =SEQUENCES({2,0,2,4},,,,,-1) |
| B27:G33 | B27 | =SEQUENCES({3,7,2,4,3,5},,,,1) |
| I27:N33 | I27 | =SEQUENCES({3,7,2,4,3,5},2.2,,,1) |
| P27:U33 | P27 | =SEQUENCES({3,7,2,4,3,5},-3.5,,,1) |
| B36:G42 | B36 | =SEQUENCES({3,7,2,4,3,5},,1.73,,1) |
| I36:N42 | I36 | =SEQUENCES({3,7,2,4,3,5},,{1.73,-2},,1) |
| P36:U42 | P36 | =SEQUENCES({3,7,2,4,3,5},,{1.73,-2,0,4},,1) |
| B45:G51 | B45 | =SEQUENCES({3,7,2,4,3,5},,,0.75,1) |
| I45:N51 | I45 | =SEQUENCES({3,7,2,4,3,5},,,{0.75,2.33,1.5},1) |
| P45:U51 | P45 | =SEQUENCES({3,7,2,4,3,5},,,{0.75,2.33,1.5,1,-2.5,0,3,4},1) |
| B54:G60 | B54 | =SEQUENCES({3,7,2,4,3,5},,,,1,-1) |
| I54:N60 | I54 | =SEQUENCES({3,-7,2,4,3,5},,,,1) |
| P54:T58 | P54 | =SEQUENCES({3,0,2,4,3,5},,,,1) |
| B63 | B63 | =SEQUENCES({0},,,,1) |
| E63 | E63 | =SEQUENCES({0,0},,,,1) |
| H63:I65 | H63 | =SEQUENCES({0,-3,2},,,,1) |
| L63:M65 | L63 | =SEQUENCES({-3,2},,,,1) |
| P63:R65 | P63 | =SEQUENCES({-3,-1,-2},,,,1) |
| B68:G72 | B68 | =SEQUENCES({2,5,4,4,3,5},4.5,{-3,2,1.5,0,-2},{2.5,5,-3,0,-1,4},1) |
| I68:M73 | I68 | =SEQUENCES({2;5;4;4;3;5},4.5,{-3,2,1.5,0,-2},{2.5,5,-3,0,-1,4},1) |
| O68 | O68 | =SEQUENCES({1}) |
| Q68 | Q68 | =SEQUENCES({0,1}) |
| S68 | S68 | =SEQUENCES({0,0,1}) |
| B75:G75 | B75 | =SEQUENCES({0,-5,1}) |
| I75:J79 | I75 | =SEQUENCES({0,-5,1},,,,1) |
| L75:Q80 | L75 | =SEQUENCES({2;0;-5;0;-5;4;5;6},,3,,1) |
| Dynamic array formulas. | ||
Upvote
0
