XREPEAT is a powerful/highly versatile repeating and/or stacking solution for repeating single values, rows or columns; or stacking arrays/ranges allowing for filtering out or inserting blank repeats/stacks, partial stacks, or (partial) 'stacks of stacks'
XREPEAT works with any data type, (i.e. number, text, blank, logical, and/or error), and can be used in at least twenty-eight (and possibly even more) ways (alone or in some combinations):
1) repeating any single value at the specified number of rows (single column output) via the "rows" parameter
2) repeating any single value at the specified number of columns (single row output) via the "columns" parameter
3) repeating any single value at the specified number of rows and columns (2D output) via the "rows" and "columns" parameters
4) repeating any single value or multiple values at the specified numbers of rows (2D output) via the "rows" parameter
5) repeating any single value or multiple values at the specified numbers of columns (2D output) via the "columns" parameter
6) filtering out columns from #4 via the "rows" parameter by including zeros instead of positive numbers
7) filtering out rows from #5 via the "columns" parameter by including zeros instead of positive numbers
8) inserting blank columns in #4 via the "rows" parameter by including negative numbers
9) inserting blank rows in #5 via the "columns" parameter by including negative numbers
10) linearizing #4, 6, or 8 into a single column by including "1" in the "columns" parameter
11) generating repeat columns of #10 by increasing the "columns" parameter number from "1" to the desired number
12) linearizing #5, 7, or 9 into a single row by including "1" in the "rows" parameter
13) generating repeat rows of #12 by increasing the "rows" parameter number from "1" to the desired number
14) stacking any input reference or any of the above #1-13 scenarios at the specified single number of v_stacks via the "v_stacks" parameter
15) stacking any input reference or any of the above #1-13 scenarios at the specified multiple numbers of v_stacks via the "v_stacks" parameter
16) filtering out any of the v_stacks in #15 by including zeros in the "v_stacks" parameter
17) inserting blank v_stacks in #15 by including negative numbers in the "v_stacks" parameter
18) stacking any input reference or any of the above #1-13 scenarios at the specified single number of h_stacks via the "h_stacks" parameter
19) stacking any input reference or any of the above #1-13 scenarios at the specified multiple numbers of h_stacks via the "h_stacks" parameter
20) filtering out any of the h_stacks in #18 by including zeros in the "h_stacks" parameter
21) inserting blank h_stacks in #18 by including negative numbers in the "h_stacks" parameter
22) stacking #14-17 at the specified numbers of h_stacks via the "h_stacks" parameter
23) filtering out h_stacks from #22 by including zeros in the "h_stacks" parameter
24) inserting blank h_stacks in #22 by including negative numbers in the "h_stacks" parameter
25) stacking #18-21 at the specified numbers of v_stacks via the "v_stacks" parameter and turning on the "multi_stack" parameter
26) filtering out v_stacks from #25 by including zeros in the "v_stacks" parameter
27) inserting blank v_stacks in #25 by including negative numbers in the "v_stacks" parameter
28) generating partial stacks in #14-27 scenarios by including decimal numbers in the "v_stacks" and/or "h_stacks" parameters
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
XREPEAT takes six parameters, one required and five optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/array/range
Note that if more than one entry needs to be entered, they should be included in array brackets "{}" per native syntax
Note that any data type can be included in the reference (for a full list of all data types, refer to the latest update of XTYPE)
Note that whether the reference can be 1D or 2D depends on the desired operation/output as described above in #1-27 list; an obvious example is that "rows" and/or "columns" parameters cannot be used with 2D references as it does not make sense to repeat a 2D reference at numbers of rows and/or columns; in this particular example, the reference can be only h_stacked or v_stacked (see sections below for more details)
Note that for 1D references, it makes no difference whether the reference is entered in row format (i.e. elements separated by commas) or column format (i.e. elements separated by semicolons) (see sections B and C below for more details)
B) The second parameter, optional, specifies the number(s) of rows of repeats for the input and can be either omitted or any integer(s) (real numbers can be included as well; see below)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that there is a one-to-one correspondence between the numbers in "rows" and the elements of the "reference"
Note that if fewer numbers than the count of the reference elements are entered, only those elements of the reference that have corresponding numbers in "rows" will be repeated and the remaining reference elements will be ignored; thus, if additional numbers are later added to "rows", the reference elements corresponding to these new numbers will start to appear in the output spill
Note that if more numbers than the count of the reference elements are entered, the numbers past the last point of correspondence to the reference elements will be ignored; thus, if more elements are later added to the reference, these new elements will be repeated in the output spill as long as they are in correspondence to a number in "rows"
Note that if zero is used for any "reference" element, that element will be omitted from the output spill and not repeated
Note that non-integer values will be rounded down to the nearest integer
Note that if only zero(s) or number(s) between 0 and 1 are included in this parameter, the output will be a #CALC! error
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); thus, if only one or more negative numbers are included, the output will be a series of blanks
Note that if the reference contains only a single element, then both the "rows" parameter and the "columns" parameter (see section C below) can include multiple numbers; in this context, only the first number of each parameter will be used by the function, and the remaining numbers will be ignored
Note that if the reference is 1D row (i.e. one row or one column), either the "rows" or the "columns" parameter (see section C below), but not both, can include multiple numbers; in this context, the "columns" parameter can be omitted or include a single number:
1) if a single number is included in "columns" and is "1", the function will linearize the output of the "rows" operation and display the output as a single column
2) if the number in "columns" is more than 1, the linearized output will repeat as multiple columns at the specified number
3) if the number in "columns" is zero, the function will output a #CALC! error
4) if the number in "columns" is -1, the output will be a series of blanks at the size of the linearized output of #1
5) if the number in "columns" is less than -1, the blank output of #4 will be repeated by the absolute value of the specified number
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
C) The third parameter, optional, specifies the number(s) of columns of repeats for the input and can be either omitted or any integer(s) (real numbers can be included as well; see below)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that there is a one-to-one correspondence between the numbers in "columns" and the elements of the "reference"
Note that if fewer numbers than the count of the reference elements are entered, only those elements of the reference that have corresponding numbers in "columns" will be repeated and the remaining reference elements will be ignored; thus, if additional numbers are later added to "columns", the reference elements corresponding to these new numbers will start to appear in the output spill
Note that if more numbers than the count of the reference elements are entered, the numbers past the last point of correspondence to the reference elements will be ignored; thus, if more elements are later added to the reference, these new elements will be repeated in the output spill as long as they are in correspondence to a number in "columns"
Note that if zero is used for any "reference" element, that element will be omitted from the output spill and not repeated
Note that non-integer values will be rounded down to the nearest integer
Note that if only zero(s) or number(s) between 0 and 1 are included in this parameter, the output will be a #CALC! error
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); thus, if only one or more negative numbers are included, the output will be a series of blanks
Note that if the reference contains only a single element, then both the "columns" parameter and the "rows" parameter (see section B above) can include multiple numbers; in this context, only the first number of each parameter will be used by the function, and the remaining numbers will be ignored
Note that if the reference is 1D (i.e. one row or one column), either the "rows" or the "column" parameter, but not both, can include multiple numbers; thus, if "columns" contains multiple numbers, the "rows" parameter (see section B above) can be omitted or include a single number:
1) if a single number is included in "rows" and is "1", the function will linearize the output of the "columns" operation and display the output as a single row; however, this will not necessarily be the transposed version of the linearized output through the "rows" parameter explained above (see section B); the reason is that the function scans down the stacked output in both cases (this is done via the MREPEAT component of the function) (see all code modules below after the description)
2) if the number in "rows" is more than 1, the linearized output will repeat as multiple rows at the specified number
3) if the number in "rows" is zero, the function will output a #CALC! error
4) if the number in "rows" is -1, the function output will be a row of blanks at the size of the linearized output of #1
5) if the number in "rows" is less than -1, the blank row of #4 will be repeated by the absolute value of the specified number
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 number(s) of v_stacks of the (processed) input and can be either omitted or any real number(s) (processed refers to any changes done to the reference by the "rows" and/or "columns" and/or "h_stacks" parameters)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that if more than one number is entered, multiple sets of v_stacks will be spilled horizontally at the order specified by the numbers
Note that if any zeros are included in this parameter, those v_stacks will be eliminated from the output
Note that if only one or more zeros are included in this parameter, the function will return a #CALC! error
Note that if one or more negative numbers are included, the function will generate blank v_stacks at the same size as the absolute values of the numbers
Note that if only negative numbers are included in this parameter, the output will be a series of blanks
Note that non-integer values can be used to generate portions of stacks; the fractional part of the number is used by the function (as percentage) to calculate whether it will correspond to at least "1" row of the output; if it does not, the fractional part will be considered as zero:
1) if the number in v_stacks is between 0 and 1, then if it corresponds to at least one row or more, the function will include that many rows as a portion of a stack
2) if a single number is included in v_stacks and is between 0 and 1, then if it does not correspond to at least one row, the function will return a #CLAC! error
3) if multiple numbers are included in v_stacks and are between 0 and 1, then if they do not correspond to at least one row, the function will return a #CLAC! error as well, as all the numbers will be treated as zeros
4) if the number in v_stacks is greater than 1, the row(s), if any, corresponding to the fractional portion will be added to the stack(s) resulting from the whole number portion
5) for negative numbers in v_stacks, the blank version of the stack of the size of #1 or #4 will be included in the output
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 number(s) of h_stacks of the (processed) input and can be either omitted or any real number(s) (processed refers to any changes done to the reference by the "rows" and/or "columns" and/or "v_stacks" parameters)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that if more than one number is entered, multiple sets of h_stacks will be spilled vertically at the order specified by the numbers
Note that if any zeros are included in this parameter, those h_stacks will be eliminated from the output
Note that if only one or more zeros are included in this parameter, the function will return a #CALC! error
Note that if one or more negative numbers are included, the function will generate blank h_stacks at the same size as the absolute values of the numbers
Note that if only negative numbers are included in this parameter, the output will be a series of blanks
Note that non-integer values can be used to generate portions of stacks; the fractional part of the number is used by the function (as percentage) to calculate whether it will correspond to at least "1" column of the output; if it does not, the fractional part will be considered as zero:
1) if the number in h_stacks is between 0 and 1, then if it corresponds to at least one column or more, the function will include that many columns as a portion of a stack
2) if a single number is included in h_stacks and is between 0 and 1, then if it does not correspond to at least one column, the function will return a #CLAC! error
3) if multiple numbers are included in h_stacks and are between 0 and 1, then if they do not correspond to at least one column, the function will return a #CLAC! error as well, as all the numbers will be treated as zeros
4) if the number in h_stacks is greater than 1, the column(s), if any, corresponding to the fractional portion will be added to the stack(s) resulting from the whole number portion
5) for negative numbers in h_stacks, the blank version of the stack of the size of #1 or #2 will be included in the output
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
F) The sixth parameter, optional, functions when both the "v_stacks" and "h_stacks" parameters are used simultaneously, in order to distinguish between 'h_stacks of v_stacks' and 'v_stacks of h_stacks' and takes two general arguments:
0 or omitted or FALSE, for 'h_stacks of v_stacks' (i.e. the function will first generate v_stacks of the (processed) input and then use it to generate h_stacks)
1 or TRUE or any number other than 0, for 'v_stacks of h_stacks' (i.e. the function will first generate h_stacks of the (processed) input and then use it to generate v_stacks)
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
This function uses the recursive LAMBDA method developed by @djclements to handle the multiple stacks, discussed in the following thread:
www.mrexcel.com
Special thanks to @djclements for developing this amazing and efficient method that I have coded as my JBH module (see below)
(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 there are a couple of IF functions in the XREPEAT formula that are not strictly necessary for the functionality and are there to check for valid insertion of arguments such that if the wrong arguments are entered, the function crashes to an #N/A; this is to ensure no erroneous results are generated by mistake.
XREPEAT
JBG (multi-stacking core)
JBH (stacking core)
MREPEAT (posted separately)
SREPEAT (posted separately)
ISNUMBERS (Returns TRUE if ALL elements in the reference are numbers and FALSE otherwise)
IO (shortened form of ISOMITTED)
XREPEAT works with any data type, (i.e. number, text, blank, logical, and/or error), and can be used in at least twenty-eight (and possibly even more) ways (alone or in some combinations):
1) repeating any single value at the specified number of rows (single column output) via the "rows" parameter
2) repeating any single value at the specified number of columns (single row output) via the "columns" parameter
3) repeating any single value at the specified number of rows and columns (2D output) via the "rows" and "columns" parameters
4) repeating any single value or multiple values at the specified numbers of rows (2D output) via the "rows" parameter
5) repeating any single value or multiple values at the specified numbers of columns (2D output) via the "columns" parameter
6) filtering out columns from #4 via the "rows" parameter by including zeros instead of positive numbers
7) filtering out rows from #5 via the "columns" parameter by including zeros instead of positive numbers
8) inserting blank columns in #4 via the "rows" parameter by including negative numbers
9) inserting blank rows in #5 via the "columns" parameter by including negative numbers
10) linearizing #4, 6, or 8 into a single column by including "1" in the "columns" parameter
11) generating repeat columns of #10 by increasing the "columns" parameter number from "1" to the desired number
12) linearizing #5, 7, or 9 into a single row by including "1" in the "rows" parameter
13) generating repeat rows of #12 by increasing the "rows" parameter number from "1" to the desired number
14) stacking any input reference or any of the above #1-13 scenarios at the specified single number of v_stacks via the "v_stacks" parameter
15) stacking any input reference or any of the above #1-13 scenarios at the specified multiple numbers of v_stacks via the "v_stacks" parameter
16) filtering out any of the v_stacks in #15 by including zeros in the "v_stacks" parameter
17) inserting blank v_stacks in #15 by including negative numbers in the "v_stacks" parameter
18) stacking any input reference or any of the above #1-13 scenarios at the specified single number of h_stacks via the "h_stacks" parameter
19) stacking any input reference or any of the above #1-13 scenarios at the specified multiple numbers of h_stacks via the "h_stacks" parameter
20) filtering out any of the h_stacks in #18 by including zeros in the "h_stacks" parameter
21) inserting blank h_stacks in #18 by including negative numbers in the "h_stacks" parameter
22) stacking #14-17 at the specified numbers of h_stacks via the "h_stacks" parameter
23) filtering out h_stacks from #22 by including zeros in the "h_stacks" parameter
24) inserting blank h_stacks in #22 by including negative numbers in the "h_stacks" parameter
25) stacking #18-21 at the specified numbers of v_stacks via the "v_stacks" parameter and turning on the "multi_stack" parameter
26) filtering out v_stacks from #25 by including zeros in the "v_stacks" parameter
27) inserting blank v_stacks in #25 by including negative numbers in the "v_stacks" parameter
28) generating partial stacks in #14-27 scenarios by including decimal numbers in the "v_stacks" and/or "h_stacks" parameters
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
XREPEAT takes six parameters, one required and five optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/array/range
Note that if more than one entry needs to be entered, they should be included in array brackets "{}" per native syntax
Note that any data type can be included in the reference (for a full list of all data types, refer to the latest update of XTYPE)
Note that whether the reference can be 1D or 2D depends on the desired operation/output as described above in #1-27 list; an obvious example is that "rows" and/or "columns" parameters cannot be used with 2D references as it does not make sense to repeat a 2D reference at numbers of rows and/or columns; in this particular example, the reference can be only h_stacked or v_stacked (see sections below for more details)
Note that for 1D references, it makes no difference whether the reference is entered in row format (i.e. elements separated by commas) or column format (i.e. elements separated by semicolons) (see sections B and C below for more details)
B) The second parameter, optional, specifies the number(s) of rows of repeats for the input and can be either omitted or any integer(s) (real numbers can be included as well; see below)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that there is a one-to-one correspondence between the numbers in "rows" and the elements of the "reference"
Note that if fewer numbers than the count of the reference elements are entered, only those elements of the reference that have corresponding numbers in "rows" will be repeated and the remaining reference elements will be ignored; thus, if additional numbers are later added to "rows", the reference elements corresponding to these new numbers will start to appear in the output spill
Note that if more numbers than the count of the reference elements are entered, the numbers past the last point of correspondence to the reference elements will be ignored; thus, if more elements are later added to the reference, these new elements will be repeated in the output spill as long as they are in correspondence to a number in "rows"
Note that if zero is used for any "reference" element, that element will be omitted from the output spill and not repeated
Note that non-integer values will be rounded down to the nearest integer
Note that if only zero(s) or number(s) between 0 and 1 are included in this parameter, the output will be a #CALC! error
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); thus, if only one or more negative numbers are included, the output will be a series of blanks
Note that if the reference contains only a single element, then both the "rows" parameter and the "columns" parameter (see section C below) can include multiple numbers; in this context, only the first number of each parameter will be used by the function, and the remaining numbers will be ignored
Note that if the reference is 1D row (i.e. one row or one column), either the "rows" or the "columns" parameter (see section C below), but not both, can include multiple numbers; in this context, the "columns" parameter can be omitted or include a single number:
1) if a single number is included in "columns" and is "1", the function will linearize the output of the "rows" operation and display the output as a single column
2) if the number in "columns" is more than 1, the linearized output will repeat as multiple columns at the specified number
3) if the number in "columns" is zero, the function will output a #CALC! error
4) if the number in "columns" is -1, the output will be a series of blanks at the size of the linearized output of #1
5) if the number in "columns" is less than -1, the blank output of #4 will be repeated by the absolute value of the specified number
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
C) The third parameter, optional, specifies the number(s) of columns of repeats for the input and can be either omitted or any integer(s) (real numbers can be included as well; see below)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that there is a one-to-one correspondence between the numbers in "columns" and the elements of the "reference"
Note that if fewer numbers than the count of the reference elements are entered, only those elements of the reference that have corresponding numbers in "columns" will be repeated and the remaining reference elements will be ignored; thus, if additional numbers are later added to "columns", the reference elements corresponding to these new numbers will start to appear in the output spill
Note that if more numbers than the count of the reference elements are entered, the numbers past the last point of correspondence to the reference elements will be ignored; thus, if more elements are later added to the reference, these new elements will be repeated in the output spill as long as they are in correspondence to a number in "columns"
Note that if zero is used for any "reference" element, that element will be omitted from the output spill and not repeated
Note that non-integer values will be rounded down to the nearest integer
Note that if only zero(s) or number(s) between 0 and 1 are included in this parameter, the output will be a #CALC! error
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); thus, if only one or more negative numbers are included, the output will be a series of blanks
Note that if the reference contains only a single element, then both the "columns" parameter and the "rows" parameter (see section B above) can include multiple numbers; in this context, only the first number of each parameter will be used by the function, and the remaining numbers will be ignored
Note that if the reference is 1D (i.e. one row or one column), either the "rows" or the "column" parameter, but not both, can include multiple numbers; thus, if "columns" contains multiple numbers, the "rows" parameter (see section B above) can be omitted or include a single number:
1) if a single number is included in "rows" and is "1", the function will linearize the output of the "columns" operation and display the output as a single row; however, this will not necessarily be the transposed version of the linearized output through the "rows" parameter explained above (see section B); the reason is that the function scans down the stacked output in both cases (this is done via the MREPEAT component of the function) (see all code modules below after the description)
2) if the number in "rows" is more than 1, the linearized output will repeat as multiple rows at the specified number
3) if the number in "rows" is zero, the function will output a #CALC! error
4) if the number in "rows" is -1, the function output will be a row of blanks at the size of the linearized output of #1
5) if the number in "rows" is less than -1, the blank row of #4 will be repeated by the absolute value of the specified number
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 number(s) of v_stacks of the (processed) input and can be either omitted or any real number(s) (processed refers to any changes done to the reference by the "rows" and/or "columns" and/or "h_stacks" parameters)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that if more than one number is entered, multiple sets of v_stacks will be spilled horizontally at the order specified by the numbers
Note that if any zeros are included in this parameter, those v_stacks will be eliminated from the output
Note that if only one or more zeros are included in this parameter, the function will return a #CALC! error
Note that if one or more negative numbers are included, the function will generate blank v_stacks at the same size as the absolute values of the numbers
Note that if only negative numbers are included in this parameter, the output will be a series of blanks
Note that non-integer values can be used to generate portions of stacks; the fractional part of the number is used by the function (as percentage) to calculate whether it will correspond to at least "1" row of the output; if it does not, the fractional part will be considered as zero:
1) if the number in v_stacks is between 0 and 1, then if it corresponds to at least one row or more, the function will include that many rows as a portion of a stack
2) if a single number is included in v_stacks and is between 0 and 1, then if it does not correspond to at least one row, the function will return a #CLAC! error
3) if multiple numbers are included in v_stacks and are between 0 and 1, then if they do not correspond to at least one row, the function will return a #CLAC! error as well, as all the numbers will be treated as zeros
4) if the number in v_stacks is greater than 1, the row(s), if any, corresponding to the fractional portion will be added to the stack(s) resulting from the whole number portion
5) for negative numbers in v_stacks, the blank version of the stack of the size of #1 or #4 will be included in the output
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 number(s) of h_stacks of the (processed) input and can be either omitted or any real number(s) (processed refers to any changes done to the reference by the "rows" and/or "columns" and/or "v_stacks" parameters)
Note that if more than one number needs to be entered, they should be included in array brackets "{}" per native syntax
Note that the numbers in this parameter can be entered in row or column format as the function linearizes them (i.e. {2,4,3} and {2;4;3} will be the same)
Note that if more than one number is entered, multiple sets of h_stacks will be spilled vertically at the order specified by the numbers
Note that if any zeros are included in this parameter, those h_stacks will be eliminated from the output
Note that if only one or more zeros are included in this parameter, the function will return a #CALC! error
Note that if one or more negative numbers are included, the function will generate blank h_stacks at the same size as the absolute values of the numbers
Note that if only negative numbers are included in this parameter, the output will be a series of blanks
Note that non-integer values can be used to generate portions of stacks; the fractional part of the number is used by the function (as percentage) to calculate whether it will correspond to at least "1" column of the output; if it does not, the fractional part will be considered as zero:
1) if the number in h_stacks is between 0 and 1, then if it corresponds to at least one column or more, the function will include that many columns as a portion of a stack
2) if a single number is included in h_stacks and is between 0 and 1, then if it does not correspond to at least one column, the function will return a #CLAC! error
3) if multiple numbers are included in h_stacks and are between 0 and 1, then if they do not correspond to at least one column, the function will return a #CLAC! error as well, as all the numbers will be treated as zeros
4) if the number in h_stacks is greater than 1, the column(s), if any, corresponding to the fractional portion will be added to the stack(s) resulting from the whole number portion
5) for negative numbers in h_stacks, the blank version of the stack of the size of #1 or #2 will be included in the output
Note that if anything other than numbers is entered in this parameter, the function will crash to an #N/A
F) The sixth parameter, optional, functions when both the "v_stacks" and "h_stacks" parameters are used simultaneously, in order to distinguish between 'h_stacks of v_stacks' and 'v_stacks of h_stacks' and takes two general arguments:
0 or omitted or FALSE, for 'h_stacks of v_stacks' (i.e. the function will first generate v_stacks of the (processed) input and then use it to generate h_stacks)
1 or TRUE or any number other than 0, for 'v_stacks of h_stacks' (i.e. the function will first generate h_stacks of the (processed) input and then use it to generate v_stacks)
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
This function uses the recursive LAMBDA method developed by @djclements to handle the multiple stacks, discussed in the following thread:
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
Hello, Here is a complex vstacking question 😁 I came up with the following formula, based on LAMBDA/REDUCE, to spill multiple vstacks of the input array side by side. (I came up with another method as well, but dropped it as it was too slow on large sizes.) =LET(...
Special thanks to @djclements for developing this amazing and efficient method that I have coded as my JBH module (see below)
(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 there are a couple of IF functions in the XREPEAT formula that are not strictly necessary for the functionality and are there to check for valid insertion of arguments such that if the wrong arguments are entered, the function crashes to an #N/A; this is to ensure no erroneous results are generated by mistake.
XREPEAT
Excel Formula:
=LAMBDA(reference,[rows],[columns],[v_stacks],[h_stacks],[multi_stack],
LET(
x,IFERROR(reference,""),
h,IF(reference="","",reference),
f,TOROW(h),
b,ROWS(x),c,COLUMNS(x),
y,IF(IO(rows),1,TOROW(rows)),
z,IF(IO(columns),1,TOCOL(columns)),
d,IF(IO(rows),0,COLUMNS(y)),
e,IF(IO(columns),0,ROWS(z)),
s,IF(AND(IO(rows),IO(columns)),0,IF(AND(b=1,c=1),1,2)),
g,INDEX(IF(IO(multi_stack),0,multi_stack),1),
a,IF(
OR(
AND(b=1,c=1,d>=0,e>=0),
AND(XOR(b>1,c>1),XOR(d>1,e>1)),
AND(XOR(b>1,c>1),OR(d=0,e=0,d=1,e=1)),
AND(b>1,c>1,d=0,e=0)
),
SWITCH(s,
0,reference,
1,SREPEAT(f,INDEX(y,1),INDEX(z,1)),
LET(
p,MREPEAT(
IF(d>e,f,TRANSPOSE(f)),
IF(
d>e,
IF(d>c,IF(c=1,y,INDEX(y,SEQUENCE(,c))),y),
IF(e>b,IF(b=1,z,INDEX(z,SEQUENCE(b))),z)
),
IF(
d>e,
IF(AND(e=1,ABS(z)>0),0,1),
IF(AND(d=1,ABS(y)>0),0,1)
)
),
IF(
d>=e,
IF(
IO(columns),
p,
JBG(
IF(INDEX(z,1)<0,IF(SEQUENCE(ROWS(p)),""),p),
,
ABS(z)
)
),
IF(
IO(rows),
p,
IF(
INDEX(y,1)=1,
p,
JBG(
IF(INDEX(y,1)<0,IF(SEQUENCE(,COLUMNS(p)),""),p),
ABS(y)
)
)
)
)
)
),
NA()
),
IF(
AND(
ISNUMBERS(v_stacks,TRUE),
ISNUMBERS(h_stacks,TRUE),
OR(ISNUMBERS(g,TRUE),ISLOGICAL(g)),
AND(IFERROR(ROWS(multi_stack),1)=1,IFERROR(COLUMNS(multi_stack),1)=1)
),
IF(
AND(IO(v_stacks),IO(h_stacks)),
a,
JBG(
a,
IF(v_stacks>=1,v_stacks,IF(v_stacks<=0,v_stacks,IF((v_stacks*ROWS(a))<1,0,v_stacks))),
IF(h_stacks>=1,h_stacks,IF(h_stacks<=0,h_stacks,IF((h_stacks*COLUMNS(a))<1,0,h_stacks))),
g
)
),
NA()
)
)
)JBG (multi-stacking core)
Excel Formula:
=LAMBDA(reference,[v_stacks],[h_stacks],[multi_stack],
LET(
a,reference,
c,IF(
AND(IO(v_stacks),IO(h_stacks)),
1,
IF(
OR(IO(v_stacks),IO(h_stacks)),
IF(IO(h_stacks),2,3),
IF(multi_stack,5,4)
)
),
d,IF(OR(c=2,c=4),1,IF(OR(c=3,c=5),0)),
o,TOCOL(FILTER(v_stacks,v_stacks<>0)),
p,TOROW(FILTER(h_stacks,h_stacks<>0)),
b,IF(d,o,p),
r,ROWS(a),
w,COLUMNS(a),
h,MAX(ABS(b))*IF(d,r,w),
z,JBH(a,b,d,r,w,h),
e,IF(OR(c=4,c=5),1,0),
g,IF(
e,
LET(
j,IF(c=5,1,IF(c=4,0)),
q,IF(j,o,p),
l,ROWS(z),
m,COLUMNS(z),
s,MAX(ABS(q))*IF(j,l,m),
JBH(z,q,j,l,m,s)
)
),
IF(c=1,a,IF(OR(c=2,c=3),z,g)))
)JBH (stacking core)
Excel Formula:
=LAMBDA(reference,stacks,v_or_h,rows,columns,spill_size,
LET(
a,reference,
b,stacks,
d,v_or_h,
r,rows,
w,columns,
h,spill_size,
i,LAMBDA(n,
EXPAND(
IF(
n<0,
"",
INDEX(
a,
IF(
d,
MOD(SEQUENCE(n*r,1,0),r)+1,
SEQUENCE(r,1)
),
IF(
d,
SEQUENCE(1,w),
MOD(SEQUENCE(1,n*w,0),w)+1
)
)
),
IF(d,h,r),
IF(d,w,h),
""
)
),
f,LAMBDA(x,y,
IF(
IF(d,ROWS(y),COLUMNS(y))=1,
i(@y),
IF(
d,
HSTACK(
x(x,TAKE(y,ROWS(y)/2)),
x(x,DROP(y,ROWS(y)/2))
),
VSTACK(
x(x,TAKE(y,,COLUMNS(y)/2)),
x(x,DROP(y,,COLUMNS(y)/2))
)
)
)
),
f(f,b)
)
)MREPEAT (posted separately)
SREPEAT (posted separately)
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)
)| XREPEAT.xlsx | ||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | |||
| 1 | ||||||||||||||||||||||||||||||||||||||||
| 2 | #CALC! | |||||||||||||||||||||||||||||||||||||||
| 3 | ||||||||||||||||||||||||||||||||||||||||
| 4 | reference | columns | output | reference | 23 | "book" | "" | TRUE | FALSE | #NULL! | #DIV/0! | #VALUE! | #REF! | #NAME? | #NUM! | #N/A | ######## | rows | columns | output | columns | v_stacks | h_stacks | output | ||||||||||||||||
| 5 | 23 | 3 | 23 | 23 | 23 | rows | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | omitted | {3,2,4,2,3} | 23 | 23 | 23 | 1 | 3 | omitted | 91.2 | ||||||||||||
| 6 | "book" | 3 | book | book | book | output | 23 | book | TRUE | FALSE | #NULL! | #DIV/0! | #VALUE! | #REF! | #NAME? | #NUM! | #N/A | ######## | #DIV/0! | #DIV/0! | 91.2 | |||||||||||||||||||
| 7 | "" | 3 | 23 | book | TRUE | FALSE | #NULL! | #DIV/0! | #VALUE! | #REF! | #NAME? | #NUM! | #N/A | ######## | book | book | book | book | 91.2 | |||||||||||||||||||||
| 8 | TRUE | 3 | TRUE | TRUE | TRUE | 23 | book | TRUE | FALSE | #NULL! | #DIV/0! | #VALUE! | #REF! | #NAME? | #NUM! | #N/A | ######## | #REF! | ||||||||||||||||||||||
| 9 | FALSE | 3 | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | #REF! | |||||||||||||||||||||||||||||||
| 10 | #NULL! | 3 | #NULL! | #NULL! | #NULL! | rows | columns | output | ||||||||||||||||||||||||||||||||
| 11 | #DIV/0! | 3 | #DIV/0! | #DIV/0! | #DIV/0! | omitted | {3;2;4;2;3} | 23 | 23 | 23 | ||||||||||||||||||||||||||||||
| 12 | #VALUE! | 3 | #VALUE! | #VALUE! | #VALUE! | #DIV/0! | #DIV/0! | |||||||||||||||||||||||||||||||||
| 13 | #REF! | 3 | #REF! | #REF! | #REF! | book | book | book | book | FALSE | ||||||||||||||||||||||||||||||
| 14 | #NAME? | 3 | #NAME? | #NAME? | #NAME? | FALSE | ||||||||||||||||||||||||||||||||||
| 15 | #NUM! | 3 | #NUM! | #NUM! | #NUM! | TRUE | TRUE | TRUE | 91.2 | |||||||||||||||||||||||||||||||
| 16 | #N/A | 3 | #N/A | #N/A | #N/A | 1 | {3;2;4;2;3} | 23 | #DIV/0! | book | TRUE | 23 | #DIV/0! | book | TRUE | 23 | book | TRUE | book | 91.2 | ||||||||||||||||||||
| 17 | ######## | 3 | ######## | ######## | ######## | 2 | {3;2;4;2;3} | 23 | #DIV/0! | book | TRUE | 23 | #DIV/0! | book | TRUE | 23 | book | TRUE | book | 91.2 | ||||||||||||||||||||
| 18 | 23 | #DIV/0! | book | TRUE | 23 | #DIV/0! | book | TRUE | 23 | book | TRUE | book | #REF! | |||||||||||||||||||||||||||
| 19 | reference | type | columns | output | {1,3} | {3;2;4;2;3} | #N/A | #REF! | ||||||||||||||||||||||||||||||||
| 20 | 23 | 1 | 3 | 23 | 23 | 23 | columns | v_stacks | h_stacks | output | ||||||||||||||||||||||||||||||
| 21 | "book" | 2 | 3 | "book" | "book" | "book" | rows | columns | output | omitted | 3 | omitted | 91.2 | #REF! | FALSE | |||||||||||||||||||||||||
| 22 | 31 | 3 | #VALUE! | {3,2,4,2,3} | omitted | 23 | #DIV/0! | book | TRUE | 91.2 | #REF! | FALSE | ||||||||||||||||||||||||||||
| 23 | 32 | 3 | 23 | #DIV/0! | book | TRUE | 91.2 | FALSE | ||||||||||||||||||||||||||||||||
| 24 | 33 | 3 | 23 | book | TRUE | 91.2 | #REF! | FALSE | FALSE | |||||||||||||||||||||||||||||||
| 25 | 34 | 3 | book | 91.2 | #REF! | FALSE | 91.2 | |||||||||||||||||||||||||||||||||
| 26 | TRUE | 41 | 3 | TRUE | TRUE | TRUE | {3,2,4,2,3} | 1 | 23 | 91.2 | 91.2 | |||||||||||||||||||||||||||||
| 27 | FALSE | 42 | 3 | FALSE | FALSE | FALSE | 23 | 91.2 | #REF! | FALSE | 91.2 | |||||||||||||||||||||||||||||
| 28 | #NULL! | 161 | 3 | #NULL! | #NULL! | #NULL! | 23 | 91.2 | #REF! | FALSE | #REF! | |||||||||||||||||||||||||||||
| 29 | #DIV/0! | 162 | 3 | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | 91.2 | #REF! | |||||||||||||||||||||||||||||||
| 30 | #VALUE! | 163 | 3 | #VALUE! | #VALUE! | #VALUE! | #DIV/0! | omitted | omitted | 2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | ||||||||||||||||||||||||
| 31 | #REF! | 164 | 3 | #REF! | #REF! | #REF! | book | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | |||||||||||||||||||||||||||
| 32 | #NAME? | 165 | 3 | #NAME? | #NAME? | #NAME? | book | 91.2 | 91.2 | |||||||||||||||||||||||||||||||
| 33 | #NUM! | 166 | 3 | #NUM! | #NUM! | #NUM! | book | omitted | 3 | 2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | FALSE | |||||||||||||||||||||||
| 34 | #N/A | 167 | 3 | #N/A | #N/A | #N/A | book | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | FALSE | ||||||||||||||||||||||||||
| 35 | ######## | 168 | 3 | ######## | ######## | ######## | 91.2 | 91.2 | 1 | omitted | 2 | 91.2 | 91.2 | |||||||||||||||||||||||||||
| 36 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | ||||||||||||||||||||||||||||||||
| 37 | reference | rows | columns | output | TRUE | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | |||||||||||||||||||||||||||
| 38 | 23 | 3 | 3 | 23 | 23 | 23 | TRUE | 91.2 | 91.2 | #REF! | #REF! | |||||||||||||||||||||||||||||
| 39 | 23 | 23 | 23 | TRUE | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | #REF! | #REF! | ||||||||||||||||||||||||||||
| 40 | 23 | 23 | 23 | {3,2,4,2,3} | 2 | 23 | 23 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | |||||||||||||||||||||||||||
| 41 | "book" | 3 | 3 | book | book | book | 23 | 23 | 91.2 | 91.2 | ||||||||||||||||||||||||||||||
| 42 | book | book | book | 23 | 23 | |||||||||||||||||||||||||||||||||||
| 43 | book | book | book | #DIV/0! | #DIV/0! | rows | omitted | omitted | omitted | FALSE | FALSE | |||||||||||||||||||||||||||||
| 44 | "" | 3 | 3 | #DIV/0! | #DIV/0! | v_stacks | 3 | omitted | 3 | FALSE | FALSE | |||||||||||||||||||||||||||||
| 45 | book | book | h_stacks | omitted | 2 | 2 | 1 | 3 | 2 | 91.2 | 91.2 | |||||||||||||||||||||||||||||
| 46 | book | book | output | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | ||||||||||||||||||||
| 47 | TRUE | 3 | 3 | TRUE | TRUE | TRUE | book | book | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | 91.2 | 91.2 | ||||||||||||||||||||
| 48 | TRUE | TRUE | TRUE | book | book | #REF! | #REF! | |||||||||||||||||||||||||||||||||
| 49 | TRUE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | #REF! | #REF! | |||||||||||||||||||||||||
| 50 | FALSE | 3 | 3 | FALSE | FALSE | FALSE | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | |||||||||||||||||||||||||
| 51 | FALSE | FALSE | FALSE | TRUE | TRUE | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | |||||||||||||||||||||||||||||
| 52 | FALSE | FALSE | FALSE | TRUE | TRUE | |||||||||||||||||||||||||||||||||||
| 53 | #NULL! | 3 | 3 | #NULL! | #NULL! | #NULL! | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | ||||||||||||||||||||||||
| 54 | #NULL! | #NULL! | #NULL! | {3,2,4,2,3} | {1,2} | #N/A | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | 91.2 | FALSE | FALSE | |||||||||||||||||||||||
| 55 | #NULL! | #NULL! | #NULL! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | 91.2 | 91.2 | |||||||||||||||||||||||||||||
| 56 | #DIV/0! | 3 | 3 | #DIV/0! | #DIV/0! | #DIV/0! | 91.2 | 91.2 | ||||||||||||||||||||||||||||||||
| 57 | #DIV/0! | #DIV/0! | #DIV/0! | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | 91.2 | 91.2 | |||||||||||||||||||||||||||||
| 58 | #DIV/0! | #DIV/0! | #DIV/0! | #REF! | #REF! | |||||||||||||||||||||||||||||||||||
| 59 | #VALUE! | 3 | 3 | #VALUE! | #VALUE! | #VALUE! | rows | v_stacks | h_stacks | output | #REF! | #REF! | ||||||||||||||||||||||||||||
| 60 | #VALUE! | #VALUE! | #VALUE! | 1 | 3 | omitted | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | |||||||||||||||||||||||||||
| 61 | #VALUE! | #VALUE! | #VALUE! | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | ||||||||||||||||||||||||||||||
| 62 | #REF! | 3 | 3 | #REF! | #REF! | #REF! | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | |||||||||||||||||||||||||||
| 63 | #REF! | #REF! | #REF! | 1 | omitted | 2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | FALSE | FALSE | ||||||||||||||||||
| 64 | #REF! | #REF! | #REF! | 1 | 3 | 2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | FALSE | FALSE | ||||||||||||||||||
| 65 | #NAME? | 3 | 3 | #NAME? | #NAME? | #NAME? | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | 91.2 | ||||||||||||||||||
| 66 | #NAME? | #NAME? | #NAME? | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | #REF! | FALSE | 91.2 | #REF! | FALSE | 91.2 | 91.2 | 91.2 | |||||||||||||||||||||
| 67 | #NAME? | #NAME? | #NAME? | 91.2 | 91.2 | |||||||||||||||||||||||||||||||||||
| 68 | #NUM! | 3 | 3 | #NUM! | #NUM! | #NUM! | #REF! | #REF! | ||||||||||||||||||||||||||||||||
| 69 | #NUM! | #NUM! | #NUM! | #REF! | #REF! | |||||||||||||||||||||||||||||||||||
| 70 | #NUM! | #NUM! | #NUM! | |||||||||||||||||||||||||||||||||||||
| 71 | #N/A | 3 | 3 | #N/A | #N/A | #N/A | ||||||||||||||||||||||||||||||||||
| 72 | #N/A | #N/A | #N/A | |||||||||||||||||||||||||||||||||||||
| 73 | #N/A | #N/A | #N/A | FALSE | FALSE | |||||||||||||||||||||||||||||||||||
| 74 | #GETTING_DATA! | 3 | 3 | ######## | ######## | ######## | FALSE | FALSE | ||||||||||||||||||||||||||||||||
| 75 | ######## | ######## | ######## | |||||||||||||||||||||||||||||||||||||
| 76 | ######## | ######## | ######## | |||||||||||||||||||||||||||||||||||||
| 77 | ||||||||||||||||||||||||||||||||||||||||
XREPEAT | ||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(reference,[rows],[columns],[v_stacks],[h_stacks],[multi_stack],LET(x,IFERROR(reference,""),h,IF(reference="","",reference),f,TOROW(h),b,ROWS(x),c,COLUMNS(x),y,IF(IO(rows),1,TOROW(rows)),z,IF(IO(columns),1,TOCOL(columns)),d,IF(IO(rows),0,COLUMNS(y)),e,IF(IO(columns),0,ROWS(z)),s,IF(AND(IO(rows),IO(columns)),0,IF(AND(b=1,c=1),1,2)),g,INDEX(IF(IO(multi_stack),0,multi_stack),1),a,IF(OR(AND(b=1,c=1,d>=0,e>=0),AND(XOR(b>1,c>1),XOR(d>1,e>1)),AND(XOR(b>1,c>1),OR(d=0,e=0,d=1,e=1)),AND(b>1,c>1,d=0,e=0)),SWITCH(s,0,h,1,SREPEAT(f,INDEX(y,1),INDEX(z,1)),LET(p,MREPEAT(IF(d>e,f,TRANSPOSE(f)),IF(d>e,IF(d>c,IF(c=1,y,INDEX(y,SEQUENCE(,c))),y),IF(e>b,IF(b=1,z,INDEX(z,SEQUENCE(b))),z)),IF(d>e,IF(AND(e=1,ABS(z)>0),0,1),IF(AND(d=1,ABS(y)>0),0,1))),IF(d>=e,IF(IO(columns),p,JBG(IF(INDEX(z,1)<0,IF(SEQUENCE(ROWS(p)),""),p),,ABS(z))),IF(IO(rows),p,IF(INDEX(y,1)=1,p,JBG(IF(INDEX(y,1)<0,IF(SEQUENCE(,COLUMNS(p)),""),p),ABS(y))))))),NA()),IF(AND(ISNUMBERS(v_stacks,TRUE),ISNUMBERS(h_stacks,TRUE),OR(ISNUMBERS(g,TRUE),ISLOGICAL(g)),AND(IFERROR(ROWS(multi_stack),1)=1,IFERROR(COLUMNS(multi_stack),1)=1)),IF(AND(IO(v_stacks),IO(h_stacks)),a,JBG(a,IF(v_stacks>=1,v_stacks,IF(v_stacks<=0,v_stacks,IF((v_stacks*ROWS(a))<1,0,v_stacks))),IF(h_stacks>=1,h_stacks,IF(h_stacks<=0,h_stacks,IF((h_stacks*COLUMNS(a))<1,0,h_stacks))),g)),NA()))) |
| AB5:AE9 | AB5 | =XREPEAT({23,#DIV/0!,"book","",TRUE},,{3,2,4,2,3}) |
| AJ5:AJ34 | AJ5 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},1,3) |
| I6:I8 | I6 | =XREPEAT(23,3) |
| J6:J8 | J6 | =XREPEAT("book",3) |
| K6:K8 | K6 | =XREPEAT("",3) |
| L6:L8 | L6 | =XREPEAT(TRUE,3) |
| M6:M8 | M6 | =XREPEAT(FALSE,3) |
| N6:N8 | N6 | =XREPEAT(#NULL!,3) |
| O6:O8 | O6 | =XREPEAT(#DIV/0!,3) |
| P6:P8 | P6 | =XREPEAT(#VALUE!,3) |
| Q6:Q8 | Q6 | =XREPEAT(#REF!,3) |
| R6:R8 | R6 | =XREPEAT(#NAME?,3) |
| S6:S8 | S6 | =XREPEAT(#NUM!,3) |
| T6:T8 | T6 | =XREPEAT(#N/A,3) |
| U6:U8 | U6 | =XREPEAT(#GETTING_DATA,3) |
| K11:N15 | K11 | =XREPEAT({23;#DIV/0!;"book";"";TRUE},,{3;2;4;2;3}) |
| D5:F5 | D5 | =XREPEAT(23,,3) |
| D6:F6 | D6 | =XREPEAT("book",,3) |
| D7:F7 | D7 | =XREPEAT("",,3) |
| D8:F8 | D8 | =XREPEAT(TRUE,,3) |
| D9:F9 | D9 | =XREPEAT(FALSE,,3) |
| D10:F10 | D10 | =XREPEAT(#NULL!,,3) |
| D11:F11 | D11 | =XREPEAT(#DIV/0!,,3) |
| D12:F12 | D12 | =XREPEAT(#VALUE!,,3) |
| D13:F13 | D13 | =XREPEAT(#REF!,,3) |
| D14:F14 | D14 | =XREPEAT(#NAME?,,3) |
| D15:F15 | D15 | =XREPEAT(#NUM!,,3) |
| D16:F16 | D16 | =XREPEAT(#N/A,,3) |
| D17:F17 | D17 | =XREPEAT(#GETTING_DATA,,3) |
| K16:X16 | K16 | =XREPEAT({23;#DIV/0!;"book";"";TRUE},1,{3;2;4;2;3}) |
| K17:X18 | K17 | =XREPEAT({23;#DIV/0!;"book";"";TRUE},2,{3;2;4;2;3}) |
| K19 | K19 | =XREPEAT({23;#DIV/0!;"book";"";TRUE},{1,3},{3;2;4;2;3}) |
| T21:W29 | T21 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},,3) |
| K22:O25 | K22 | =XREPEAT({23,#DIV/0!,"book","",TRUE},{3,2,4,2,3}) |
| B23 | B23 | =IF(1,"") |
| K26:K39 | K26 | =XREPEAT({23,#DIV/0!,"book","",TRUE},{3,2,4,2,3},1) |
| T30:AA32 | T30 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},,,2) |
| T33:AA41 | T33 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},,3,2) |
| E23:G35,E22,E20:G21 | E20 | =XREPEAT(B20,,3) |
| AJ35:AK44 | AJ35 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},1,,2) |
| E38:G40 | E38 | =XREPEAT(23,3,3) |
| K40:L53 | K40 | =XREPEAT({23,#DIV/0!,"book","",TRUE},{3,2,4,2,3},2) |
| E41:G43 | E41 | =XREPEAT("book",3,3) |
| E44:G46 | E44 | =XREPEAT("",3,3) |
| AJ45:AK74 | AJ45 | =XREPEAT({91.2,#REF!,"",FALSE},{3,2,3,2},1,3,2) |
| O46:Q57 | O46 | =XREPEAT({91.2;#REF!;"";FALSE},,{3;2;3;2},3) |
| R46:W49 | R46 | =XREPEAT({91.2;#REF!;"";FALSE},,{3;2;3;2},,2) |
| X46:AC57 | X46 | =XREPEAT({91.2;#REF!;"";FALSE},,{3;2;3;2},3,2) |
| E47:G49 | E47 | =XREPEAT(TRUE,3,3) |
| E50:G52 | E50 | =XREPEAT(FALSE,3,3) |
| E53:G55 | E53 | =XREPEAT(#NULL!,3,3) |
| K54 | K54 | =XREPEAT({23,#DIV/0!,"book","",TRUE},{3,2,4,2,3},{1,2}) |
| E56:G58 | E56 | =XREPEAT(#DIV/0!,3,3) |
| E59:G61 | E59 | =XREPEAT(#VALUE!,3,3) |
| L60:U62 | L60 | =XREPEAT({91.2;#REF!;"";FALSE},1,{3;2;3;2},3) |
| E62:G64 | E62 | =XREPEAT(#REF!,3,3) |
| L63:AE63 | L63 | =XREPEAT({91.2;#REF!;"";FALSE},1,{3;2;3;2},,2) |
| L64:AE66 | L64 | =XREPEAT({91.2;#REF!;"";FALSE},1,{3;2;3;2},3,2) |
| E65:G67 | E65 | =XREPEAT(#NAME?,3,3) |
| E68:G70 | E68 | =XREPEAT(#NUM!,3,3) |
| E71:G73 | E71 | =XREPEAT(#N/A,3,3) |
| E74:G76 | E74 | =XREPEAT(#GETTING_DATA,3,3) |
| Dynamic array formulas. | ||
Upvote
0
