LIN is a powerful array/range linearization alternative to the built-in TOROW/TOCOL with several options and full control over data type inclusion/exclusion
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
LIN takes nine parameters, one required and seven optional, as follows:
A) The first parameter, required, specifies the function input and can be a cell, range, or array
B) The second parameter, optional, specifies the orientation of the output and takes two general arguments:
0 or omitted or FALSE, for horizontal (similar to TOROW)
1 or TRUE or any number other than 0, for vertical (similar to TOCOL)
Note that this argument structure matches the Excel's native way of handling on/off arguments for parameters and is accomplished by my PLSL module shown below
C) The third parameter, optional, specifies the point of linearization initialization within the range/array referencing the four corners of the range/array with the top-left corner being 0 and going clockwise (the bottom-left corner is 3) and takes four arguments:
0 or omitted, for corner 0 (similar to TOROW/TOCOL)
1, for corner 1
2, for corner 2
3, for corner 3
D) The fourth parameter, optional, specifies whether to scan the range/array by column or by row and takes two general arguments:
0 or omitted or FALSE, for scanning by row (similar scan_by_column being off in TOROW/TOCOL)
1 or TRUE or any number other than 0, for scanning by column (similar scan_by_column being on in TOROW/TOCOL)
E) The fifth parameter, optional, specifies whether to scan the range/array line-by-line (i.e. starting at the same side of the range/array for each line as at the initialization point) or continuously and takes two general arguments:
0 or omitted or FALSE, for line-by-line scanning
1 or TRUE or any number other than 0, for continuous scanning
F) The sixth parameter, optional, specifies the type(s) of data that can be excluded from the main output and takes one or more of twenty-four arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
41, for TRUE
42, for FALSE
160, for #EXTERNAL! error
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,3} for excluding numbers and blanks or {2,4,16} for excluding texts, logicals, and errors; see the attached XL2BB examples (the number of types permutations is huge; I have only shown a few examples); this parameter as well as the next (include_types) allow for a remarkable level of flexibility and full resolution for managing data types in the function output
Note that if excluding types will cause the output spill to auto-shrink. If you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H
G) The seventh parameter, optional, specifies the type(s) of data that can be included in the main output and takes one or more of twenty-four arguments, exactly like the sixth parameter in section F above
Note that both exclude_types and include_types cannot be used simultaneously
Note that using include_types (unless all types are included as by {1,2,3,4,16} for example) will also cause the main output spill to auto-shrink. Again, if you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H
H) The eighth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be used to replace any entries that were either directly excluded via the exclude_types parameter or indirectly excluded via the include_types parameter
I) The ninth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be displayed as the main function output if the output is empty, i.e. #CALC! with the message "Empty arrays are not supported"
Note that if the function output contains #CALC! which comes from an existing entry in the function input, then you will see the message "This #CALC! error comes from a precedent to this formula". This implies that this particular type of output is not actually "empty", and therefore, if there is an entry in the replace_with parameter, this entry will not be shown
(You can download my template from the link in my signature below, which makes it easier to test the functions compared to pasting the XL2BB and entering the functions manually)
LIN
JAA (core for LIN)
PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
PLSL (stands for "parameter limit, single logical)
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not logically evaluateble or more than a single entry in array brackets "{}"]))
XERROR.TYPE (posted separately)
XTYPE (posted separately)
AllTypes
IO (shortened form of ISOMITTED)
JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)
JAC (module for 1D (FILTER-based) "exclude_types/include_types/replace_with" with auto-shrink)
JAI (module for "if_empty")
JAL (switch for "include_types/exclude_types")
JAN Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
NIO [shortened form of NOT(ISOMITTED)]
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
General demo:
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
LIN takes nine parameters, one required and seven optional, as follows:
A) The first parameter, required, specifies the function input and can be a cell, range, or array
B) The second parameter, optional, specifies the orientation of the output and takes two general arguments:
0 or omitted or FALSE, for horizontal (similar to TOROW)
1 or TRUE or any number other than 0, for vertical (similar to TOCOL)
Note that this argument structure matches the Excel's native way of handling on/off arguments for parameters and is accomplished by my PLSL module shown below
C) The third parameter, optional, specifies the point of linearization initialization within the range/array referencing the four corners of the range/array with the top-left corner being 0 and going clockwise (the bottom-left corner is 3) and takes four arguments:
0 or omitted, for corner 0 (similar to TOROW/TOCOL)
1, for corner 1
2, for corner 2
3, for corner 3
D) The fourth parameter, optional, specifies whether to scan the range/array by column or by row and takes two general arguments:
0 or omitted or FALSE, for scanning by row (similar scan_by_column being off in TOROW/TOCOL)
1 or TRUE or any number other than 0, for scanning by column (similar scan_by_column being on in TOROW/TOCOL)
E) The fifth parameter, optional, specifies whether to scan the range/array line-by-line (i.e. starting at the same side of the range/array for each line as at the initialization point) or continuously and takes two general arguments:
0 or omitted or FALSE, for line-by-line scanning
1 or TRUE or any number other than 0, for continuous scanning
F) The sixth parameter, optional, specifies the type(s) of data that can be excluded from the main output and takes one or more of twenty-four arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
41, for TRUE
42, for FALSE
160, for #EXTERNAL! error
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,3} for excluding numbers and blanks or {2,4,16} for excluding texts, logicals, and errors; see the attached XL2BB examples (the number of types permutations is huge; I have only shown a few examples); this parameter as well as the next (include_types) allow for a remarkable level of flexibility and full resolution for managing data types in the function output
Note that if excluding types will cause the output spill to auto-shrink. If you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H
G) The seventh parameter, optional, specifies the type(s) of data that can be included in the main output and takes one or more of twenty-four arguments, exactly like the sixth parameter in section F above
Note that both exclude_types and include_types cannot be used simultaneously
Note that using include_types (unless all types are included as by {1,2,3,4,16} for example) will also cause the main output spill to auto-shrink. Again, if you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H
H) The eighth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be used to replace any entries that were either directly excluded via the exclude_types parameter or indirectly excluded via the include_types parameter
I) The ninth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be displayed as the main function output if the output is empty, i.e. #CALC! with the message "Empty arrays are not supported"
Note that if the function output contains #CALC! which comes from an existing entry in the function input, then you will see the message "This #CALC! error comes from a precedent to this formula". This implies that this particular type of output is not actually "empty", and therefore, if there is an entry in the replace_with parameter, this entry will not be shown
(You can download my template from the link in my signature below, which makes it easier to test the functions compared to pasting the XL2BB and entering the functions manually)
LIN
VBA Code:
=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],
LET(
o,PLSL(output_orientation),
s,PLS(PO0(start_at_corner)),
b,PLSL(scan_by),
t,PLSL(scan_type),
r,IF(reference="","",reference),
g,XTYPE(reference,3),
h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),
i,JAA(r,o,s,b,t),
j,JAA(g,o,s,b,t),
k,JAA(h,o,s,b,t),
JAI(
SWITCH(JAL(exclude_types,include_types,replace_with),
0,i,
1,JAC(exclude_types,include_types,replace_with,i,j,k)),
if_empty)
)
)JAA (core for LIN)
VBA Code:
=LAMBDA(reference,orientation,start_at_corner,scan_by,scan_type,
LET(
r,ROWS(reference),
c,COLUMNS(reference),
f,IF(reference="","",reference),
g,SEQUENCE(c,,c,-1),
h,SEQUENCE(r,,r,-1),
d,SWITCH(start_at_corner,
0,f,
1,CHOOSECOLS(f,g),
2,CHOOSECOLS(CHOOSEROWS(f,h),g),
3,CHOOSEROWS(f,h)),
e,TOROW(SWITCH(scan_by,
0,SWITCH(scan_type,
0,d,
IF(
MOD(SEQUENCE(r),2),
d,
CHOOSECOLS(d,g))),
SWITCH(scan_type,
0,d,
IF(
TRANSPOSE(MOD(SEQUENCE(c),2)),
d,
CHOOSEROWS(d,SEQUENCE(,r,r,-1))))),,scan_by),
SWITCH(orientation,
0,e,
TRANSPOSE(e))
)
)PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
VBA Code:
=LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1))PLSL (stands for "parameter limit, single logical)
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not logically evaluateble or more than a single entry in array brackets "{}"]))
VBA Code:
=LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))XERROR.TYPE (posted separately)
XTYPE (posted separately)
AllTypes
VBA Code:
={1,2,3,4,16,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614}IO (shortened form of ISOMITTED)
VBA Code:
=LAMBDA(parameter,ISOMITTED(parameter))JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)
VBA Code:
=LAMBDA(types,advanced_types_array,
LET(
basic_types_array,SWITCH(advanced_types_array,1,1,2,2,31,3,32,3,41,4,42,4,16),
MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))
)
)JAC (module for 1D (FILTER-based) "exclude_types/include_types/replace_with" with auto-shrink)
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,basic_types_array,
LET(
d,IF(IO(include_types),exclude_types,include_types),
x,JAB(d,advanced_types_array),
IF(
SUM(T1F0(ISERROR(MATCH(d,AllTypes)))),
NA(),
IF(
IO(replace_with),
FILTER(
main_array,
IF(
IO(include_types),
NOT(x),
x)),
IF(
IO(include_types),
IF(x,replace_with,main_array),
IF(x,main_array,replace_with))
)
)
)
)JAI (module for "if_empty")
VBA Code:
=LAMBDA(operation,if_empty,
IF(
JAN(if_empty),
NA(),
LET(
e,IF(ISERROR(operation),IF(ERROR.TYPE(operation)=14,1,0),0),
f,IF(ISERROR(ROWS(operation)*COLUMNS(operation)),1,0),
IF(
e+f=2,
IF(
IO(if_empty),
operation,
if_empty),
operation)
)
)
)JAL (switch for "include_types/exclude_types")
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,
IF(
JAN(replace_with),
NA(),
IFS(
AND(IO(exclude_types),IO(include_types),IO(replace_with)),0,
OR(AND(IO(include_types),NIO(exclude_types)),AND(NIO(include_types),IO(exclude_types))),1
)
)
)JAN Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
VBA Code:
=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
VBA Code:
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
VBA Code:
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))NIO [shortened form of NOT(ISOMITTED)]
VBA Code:
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code:
=LAMBDA([parameter],IF(IO(parameter),0,parameter))T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA()))General demo:
| LIN.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 | |||
| 1 | ||||||||||||||||||||||||||
| 2 | #CALC! | 1 | 2 | 1 | 4 | basic types | ||||||||||||||||||||
| 3 | 10 | 8 | #DIV/0! | 6 | 1 | dt:NUMBER | ||||||||||||||||||||
| 4 | 11 | TRUE | 13 | book | 15 | 2 | dt:TEXT | |||||||||||||||||||
| 5 | 3 | dt:BLANK | ||||||||||||||||||||||||
| 6 | orientation | start_at_corner | scan_by | scan_type | 4 | dt:LOGICAL | ||||||||||||||||||||
| 7 | omitted | omitted | omitted | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 16 | dt:ERROR | |||||||
| 8 | omitted | 0 | omitted | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 9 | omitted | 1 | omitted | omitted | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | advanced types | ||||||||
| 10 | omitted | 2 | omitted | omitted | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | 1 | dt:NUMBER | |||||||
| 11 | omitted | 3 | omitted | omitted | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | 2 | dt:TEXT | |||||||
| 12 | 0 | omitted | omitted | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 31 | dt:BLANK_REAL | |||||||
| 13 | 0 | 0 | omitted | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 32 | dt:BLANK_FORMULA | |||||||
| 14 | 0 | 1 | omitted | omitted | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | 41 | dt:LOGICAL_TRUE | |||||||
| 15 | 0 | 2 | omitted | omitted | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | 42 | dt:LOGICAL_FALSE | |||||||
| 16 | 0 | 3 | omitted | omitted | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | 160 | dt:ERROR_EXTERNAL! | |||||||
| 17 | omitted | omitted | 0 | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 161 | dt:ERROR_NULL! | |||||||
| 18 | omitted | 0 | 0 | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 162 | dt:ERROR_DIV/0! | |||||||
| 19 | omitted | 1 | 0 | omitted | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | 163 | dt:ERROR_VALUE! | |||||||
| 20 | omitted | 2 | 0 | omitted | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | 164 | dt:ERROR_REF! | |||||||
| 21 | omitted | 3 | 0 | omitted | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | 165 | dt:ERROR_NAME? | |||||||
| 22 | 0 | omitted | 0 | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 166 | dt:ERROR_NUM! | |||||||
| 23 | 0 | 0 | 0 | omitted | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 167 | dt:ERROR_N/A! | |||||||
| 24 | 0 | 1 | 0 | omitted | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | 168 | dt:ERROR_GETTING_DATA! | |||||||
| 25 | 0 | 2 | 0 | omitted | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | 169 | dt:ERROR_SPILL! | |||||||
| 26 | 0 | 3 | 0 | omitted | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | 1610 | dt:ERROR_CONNECT! | |||||||
| 27 | omitted | omitted | omitted | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 1611 | dt:ERROR_BLOCKED! | |||||||
| 28 | omitted | 0 | omitted | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | 1612 | dt:ERROR_UNKNOWN! | |||||||
| 29 | omitted | 1 | omitted | 0 | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | 1613 | dt:ERROR_FIELD! | |||||||
| 30 | omitted | 2 | omitted | 0 | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | 1614 | dt:ERROR_CALC! | |||||||
| 31 | omitted | 3 | omitted | 0 | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | |||||||||
| 32 | 0 | omitted | omitted | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 33 | 0 | 0 | omitted | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 34 | 0 | 1 | omitted | 0 | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | |||||||||
| 35 | 0 | 2 | omitted | 0 | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | |||||||||
| 36 | 0 | 3 | omitted | 0 | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | |||||||||
| 37 | omitted | omitted | 0 | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 38 | omitted | 0 | 0 | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 39 | omitted | 1 | 0 | 0 | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | |||||||||
| 40 | omitted | 2 | 0 | 0 | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | |||||||||
| 41 | omitted | 3 | 0 | 0 | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | |||||||||
| 42 | 0 | omitted | 0 | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 43 | 0 | 0 | 0 | 0 | 1 | 2 | 1 | 4 | 10 | 8 | #DIV/0! | 6 | 11 | TRUE | 13 | book | 15 | |||||||||
| 44 | 0 | 1 | 0 | 0 | 4 | 1 | 2 | 1 | 6 | #DIV/0! | 8 | 10 | 15 | book | 13 | TRUE | 11 | |||||||||
| 45 | 0 | 2 | 0 | 0 | 15 | book | 13 | TRUE | 11 | 6 | #DIV/0! | 8 | 10 | 4 | 1 | 2 | 1 | |||||||||
| 46 | 0 | 3 | 0 | 0 | 11 | TRUE | 13 | book | 15 | 10 | 8 | #DIV/0! | 6 | 1 | 2 | 1 | 4 | |||||||||
| 47 | omitted | omitted | 1 | omitted | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 48 | omitted | 0 | 1 | omitted | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 49 | omitted | 1 | 1 | omitted | 6 | 15 | 4 | #DIV/0! | book | 1 | 8 | 13 | 2 | TRUE | 1 | 10 | 11 | |||||||||
| 50 | omitted | 2 | 1 | omitted | 15 | 6 | book | #DIV/0! | 4 | 13 | 8 | 1 | TRUE | 2 | 11 | 10 | 1 | |||||||||
| 51 | omitted | 3 | 1 | omitted | 11 | 10 | 1 | TRUE | 2 | 13 | 8 | 1 | book | #DIV/0! | 4 | 15 | 6 | |||||||||
| 52 | 0 | omitted | 1 | omitted | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 53 | 0 | 0 | 1 | omitted | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 54 | 0 | 1 | 1 | omitted | 6 | 15 | 4 | #DIV/0! | book | 1 | 8 | 13 | 2 | TRUE | 1 | 10 | 11 | |||||||||
| 55 | 0 | 2 | 1 | omitted | 15 | 6 | book | #DIV/0! | 4 | 13 | 8 | 1 | TRUE | 2 | 11 | 10 | 1 | |||||||||
| 56 | 0 | 3 | 1 | omitted | 11 | 10 | 1 | TRUE | 2 | 13 | 8 | 1 | book | #DIV/0! | 4 | 15 | 6 | |||||||||
| 57 | omitted | omitted | 1 | 0 | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 58 | omitted | 0 | 1 | 0 | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 59 | omitted | 1 | 1 | 0 | 6 | 15 | 4 | #DIV/0! | book | 1 | 8 | 13 | 2 | TRUE | 1 | 10 | 11 | |||||||||
| 60 | omitted | 2 | 1 | 0 | 15 | 6 | book | #DIV/0! | 4 | 13 | 8 | 1 | TRUE | 2 | 11 | 10 | 1 | |||||||||
| 61 | omitted | 3 | 1 | 0 | 11 | 10 | 1 | TRUE | 2 | 13 | 8 | 1 | book | #DIV/0! | 4 | 15 | 6 | |||||||||
| 62 | 0 | omitted | 1 | 0 | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 63 | 0 | 0 | 1 | 0 | 1 | 10 | 11 | 2 | TRUE | 1 | 8 | 13 | 4 | #DIV/0! | book | 6 | 15 | |||||||||
| 64 | 0 | 1 | 1 | 0 | 6 | 15 | 4 | #DIV/0! | book | 1 | 8 | 13 | 2 | TRUE | 1 | 10 | 11 | |||||||||
| 65 | 0 | 2 | 1 | 0 | 15 | 6 | book | #DIV/0! | 4 | 13 | 8 | 1 | TRUE | 2 | 11 | 10 | 1 | |||||||||
| 66 | 0 | 3 | 1 | 0 | 11 | 10 | 1 | TRUE | 2 | 13 | 8 | 1 | book | #DIV/0! | 4 | 15 | 6 | |||||||||
| 67 | omitted | omitted | 1 | 1 | 1 | 10 | 11 | TRUE | 2 | 1 | 8 | 13 | book | #DIV/0! | 4 | 6 | 15 | |||||||||
| 68 | omitted | 0 | 1 | 1 | 1 | 10 | 11 | TRUE | 2 | 1 | 8 | 13 | book | #DIV/0! | 4 | 6 | 15 | |||||||||
| 69 | omitted | 1 | 1 | 1 | 6 | 15 | book | #DIV/0! | 4 | 1 | 8 | 13 | TRUE | 2 | 1 | 10 | 11 | |||||||||
| 70 | omitted | 2 | 1 | 1 | 15 | 6 | 4 | #DIV/0! | book | 13 | 8 | 1 | 2 | TRUE | 11 | 10 | 1 | |||||||||
| 71 | omitted | 3 | 1 | 1 | 11 | 10 | 1 | 2 | TRUE | 13 | 8 | 1 | 4 | #DIV/0! | book | 15 | 6 | |||||||||
| 72 | 0 | omitted | 1 | 1 | 1 | 10 | 11 | TRUE | 2 | 1 | 8 | 13 | book | #DIV/0! | 4 | 6 | 15 | |||||||||
| 73 | 0 | 0 | 1 | 1 | 1 | 10 | 11 | TRUE | 2 | 1 | 8 | 13 | book | #DIV/0! | 4 | 6 | 15 | |||||||||
| 74 | 0 | 1 | 1 | 1 | 6 | 15 | book | #DIV/0! | 4 | 1 | 8 | 13 | TRUE | 2 | 1 | 10 | 11 | |||||||||
| 75 | 0 | 2 | 1 | 1 | 15 | 6 | 4 | #DIV/0! | book | 13 | 8 | 1 | 2 | TRUE | 11 | 10 | 1 | |||||||||
| 76 | 0 | 3 | 1 | 1 | 11 | 10 | 1 | 2 | TRUE | 13 | 8 | 1 | 4 | #DIV/0! | book | 15 | 6 | |||||||||
| 77 | ||||||||||||||||||||||||||
linearization | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty))) |
| H2 | H2 | =IF(1,"") |
| G3 | G3 | =1/0 |
| F7:T7 | F7 | =LIN($D$2:$H$4) |
| F8:T8 | F8 | =LIN($D$2:$H$4,,0) |
| F9:T9 | F9 | =LIN($D$2:$H$4,,1) |
| F10:T10 | F10 | =LIN($D$2:$H$4,,2) |
| F11:T11 | F11 | =LIN($D$2:$H$4,,3) |
| F12:T12 | F12 | =LIN($D$2:$H$4,0) |
| F13:T13 | F13 | =LIN($D$2:$H$4,0,0) |
| F14:T14 | F14 | =LIN($D$2:$H$4,0,1) |
| F15:T15 | F15 | =LIN($D$2:$H$4,0,2) |
| F16:T16 | F16 | =LIN($D$2:$H$4,0,3) |
| F17:T17 | F17 | =LIN($D$2:$H$4,,,0) |
| F18:T18 | F18 | =LIN($D$2:$H$4,,0,0) |
| F19:T19 | F19 | =LIN($D$2:$H$4,,1,0) |
| F20:T20 | F20 | =LIN($D$2:$H$4,,2,0) |
| F21:T21 | F21 | =LIN($D$2:$H$4,,3,0) |
| F22:T22 | F22 | =LIN($D$2:$H$4,0,,0) |
| F23:T23 | F23 | =LIN($D$2:$H$4,0,0,0) |
| F24:T24 | F24 | =LIN($D$2:$H$4,0,1,0) |
| F25:T25 | F25 | =LIN($D$2:$H$4,0,2,0) |
| F26:T26 | F26 | =LIN($D$2:$H$4,0,3,0) |
| F27:T27 | F27 | =LIN($D$2:$H$4,,,,0) |
| F28:T28 | F28 | =LIN($D$2:$H$4,,0,,0) |
| F29:T29 | F29 | =LIN($D$2:$H$4,,1,,0) |
| F30:T30 | F30 | =LIN($D$2:$H$4,,2,,0) |
| F31:T31 | F31 | =LIN($D$2:$H$4,,3,,0) |
| F32:T32 | F32 | =LIN($D$2:$H$4,0,,,0) |
| F33:T33 | F33 | =LIN($D$2:$H$4,0,0,,0) |
| F34:T34 | F34 | =LIN($D$2:$H$4,0,1,,0) |
| F35:T35 | F35 | =LIN($D$2:$H$4,0,2,,0) |
| F36:T36 | F36 | =LIN($D$2:$H$4,0,3,,0) |
| F37:T37 | F37 | =LIN($D$2:$H$4,,,0,0) |
| F38:T38 | F38 | =LIN($D$2:$H$4,,0,0,0) |
| F39:T39 | F39 | =LIN($D$2:$H$4,,1,0,0) |
| F40:T40 | F40 | =LIN($D$2:$H$4,,2,0,0) |
| F41:T41 | F41 | =LIN($D$2:$H$4,,3,0,0) |
| F42:T42 | F42 | =LIN($D$2:$H$4,0,,0,0) |
| F43:T43 | F43 | =LIN($D$2:$H$4,0,0,0,0) |
| F44:T44 | F44 | =LIN($D$2:$H$4,0,1,0,0) |
| F45:T45 | F45 | =LIN($D$2:$H$4,0,2,0,0) |
| F46:T46 | F46 | =LIN($D$2:$H$4,0,3,0,0) |
| F47:T47 | F47 | =LIN($D$2:$H$4,,,1) |
| F48:T48 | F48 | =LIN($D$2:$H$4,,0,1) |
| F49:T49 | F49 | =LIN($D$2:$H$4,,1,1) |
| F50:T50 | F50 | =LIN($D$2:$H$4,,2,1) |
| F51:T51 | F51 | =LIN($D$2:$H$4,,3,1) |
| F52:T52 | F52 | =LIN($D$2:$H$4,0,,1) |
| F53:T53 | F53 | =LIN($D$2:$H$4,0,0,1) |
| F54:T54 | F54 | =LIN($D$2:$H$4,0,1,1) |
| F55:T55 | F55 | =LIN($D$2:$H$4,0,2,1) |
| F56:T56 | F56 | =LIN($D$2:$H$4,0,3,1) |
| F57:T57 | F57 | =LIN($D$2:$H$4,,,1,0) |
| F58:T58 | F58 | =LIN($D$2:$H$4,,0,1,0) |
| F59:T59 | F59 | =LIN($D$2:$H$4,,1,1,0) |
| F60:T60 | F60 | =LIN($D$2:$H$4,,2,1,0) |
| F61:T61 | F61 | =LIN($D$2:$H$4,,3,1,0) |
| F62:T62 | F62 | =LIN($D$2:$H$4,0,,1,0) |
| F63:T63 | F63 | =LIN($D$2:$H$4,0,0,1,0) |
| F64:T64 | F64 | =LIN($D$2:$H$4,0,1,1,0) |
| F65:T65 | F65 | =LIN($D$2:$H$4,0,2,1,0) |
| F66:T66 | F66 | =LIN($D$2:$H$4,0,3,1,0) |
| F67:T67 | F67 | =LIN($D$2:$H$4,,,1,1) |
| F68:T68 | F68 | =LIN($D$2:$H$4,,0,1,1) |
| F69:T69 | F69 | =LIN($D$2:$H$4,,1,1,1) |
| F70:T70 | F70 | =LIN($D$2:$H$4,,2,1,1) |
| F71:T71 | F71 | =LIN($D$2:$H$4,,3,1,1) |
| F72:T72 | F72 | =LIN($D$2:$H$4,0,,1,1) |
| F73:T73 | F73 | =LIN($D$2:$H$4,0,0,1,1) |
| F74:T74 | F74 | =LIN($D$2:$H$4,0,1,1,1) |
| F75:T75 | F75 | =LIN($D$2:$H$4,0,2,1,1) |
| F76:T76 | F76 | =LIN($D$2:$H$4,0,3,1,1) |
| Dynamic array formulas. | ||
Last edited:
Upvote
0
