PROCESS resizes any input reference into an array/range of any desired size (could be considered as the reverse of LIN/TOROW/TOCOL) with options for output size/style and full control over data type(s) inclusion/exclusion
PROCESS was developed primarily to enable resizing of the input reference into an array/range of any desired size, i.e. rows(s) and/or column(s). When the output is chosen to be two-dimensional (which is the main reason I developed the function), PROCESS can be considered as the reverse of LIN/TOROW/TOCOL; of course, the function has full flexibility for output size/dimension(s) which means that the output can be chosen to be linear, if needed, though for this purpose, LIN would be a much better choice as it provides several options for controlling the output linear array's style.
You control the output dimension(s) by specifying the "size" of one of the dimensions (i.e. either rows or columns) of the desired output array/range, and the function will automatically determine the minimum "size" of the other dimension. Note that depending on the size of the input and the size of the desired output dimension(s), the function may append one or more blank cells at the end of the array/range (towards the bottom/right) (see the first two attached XL2BB examples); I refer to these blank cells as non-core blank cells. These non-core blank cells are highlighted in gray in the provided examples, and they will never occur in more than one row in the output (i.e. they may only occur as one partial row). Note that if you specify a "size" that will result in a spill of more rows than needed to accommodate the spill, the function will automatically exclude the extra rows and generate the minimum-size spill necessary (which is the reason why non-core blank cells will never exceed one row).
Furthermore, instead of (or in addition to) determining the output dimensions of the array, you can utilize PROCESS to have full control over data type(s) inclusion/exclusion in the output spill (hence the name "process"). For this purpose, simply leave the "size" parameter unused; note that if you don't use the "size" parameter, the output spill will have the same general dimensions of the input which may, of course, auto-shrink depending on a) what data type(s) are included/excluded and b) on the "output_style"; see the parameter descriptions below. This particular way of utilizing PROCESS could be very useful if you need to prepare (i.e. "process") the input data in order to be used with any of the built-in Excel functions without having to go through the hassle of controlling data type(s) inclusion/exclusion within those functions (by writing additional code) given that Excel's built-in functions do not have this kind of straightforward/full-resolution control over data types (some built-in functions have basic data exclusion feature such as "ignore blanks" or "ignore errors", but that is rudimentary compared with the "exclude_types/include_types/replace_with" system that I have developed). Note that most of the functions that I have developed have this kind of full-resolution data "processing" control for inclusion/exclusion already built into them, so you don't need to use PROCESS with/for them.
PROCESS takes eight parameters, one required, and seven optional, as follows:
A) The first parameter, reference, required, specifies the function input and can be a cell, range, or array
Note that if none of the other parameters are utilized, the function will spill the input reference exactly in its original form
B) The second parameter, size, specifies the number of the output rows or columns and takes a single number (integer greater than or equal to 1) as argument
Note that whether "size" refers to rows or columns is controlled by the next (i.e. third) parameter, "columns", as described below
Note that if more than one number is entered through the array brackets "{}", or if any other "type" of element(s) is entered, the function will return a single #N/A
C) The third parameter, columns, optional, specifies whether the second parameter, i.e. "size", applies to rows or columns and takes two general arguments:
0 or omitted or FALSE, for "off" meaning that the "size" specified in the second parameter refers to rows
1 or TRUE or any number other than 0, for "on" meaning that the "size" specified in the second parameter refers to columns
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
Note that if this parameter is used without using the second parameter, the function will return a single #N/A
Note that if more than one entry is included in this parameter via the array brackets "{}", the function will crash with a single #N/A
D) The fourth parameter, output_style, optional, specifies how the blank cells generated through data exclusion/inclusion are handled and takes two arguments:
0 or omitted, for removing all blank cells, if any, generated through data exclusion/inclusion
1, for removing only full rows or columns, if any, of blank cells generated through data exclusion/inclusion
Note that this parameter takes effect only if the "exclude_types" or "include_types" parameters is used and without the "replace_with" parameter (see below for descriptions). If no cells are excluded/included or if the excluded/included cells are replaced with other data through the "replace_with" parameter, then "output_style" being omitted, 0, or 1 will result in the same output spill.
Note that argument 0 or omitted attempts to be faithful to the specified "size" and it simply joins the output cells together without preserving the data structure of the unexcluded/unincluded spill, which means that cells that were in different rows of the unexcluded/unincluded spill may now appear on the same row of the excluded/included output (see attached XL2BB examples). However, argument 1 attempts to be faithful to the data structure of the unexcluded/unincluded spill, which is why it removes only full rows/columns of blank cells generated through data exclusion/inclusion. This means that the excluded/included output may not necessarily have the specified "size". Thus, the decision as to which argument to use depends on whether the "size" is more important for the particular usage or the data "structure" of the unexcluded/unincluded spill. Of course, the overall linear order of the input cells, nonetheless, is still preserved with either argument (i.e. no scrambling occurs).
Note that both arguments 0 or omitted and 1 may cause the output spill to auto-shrink. For argument 0, the auto-shrink happens at the bottom right corner of the output relative to the unexcluded/unincluded spill. For argument 1, the auto-shrink may happen for rows (upward auto-shrink) and/or columns (leftward auto-shrink) of the output relative to the unexcluded/unincluded spill (see attached XL2BB examples).
E) The fifth parameter, exclude_types, 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,31} for excluding numbers and real blanks, respectively, or {16,2,4} for excluding errors, texts, and logicals, respectively; 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 excluding type(s) may cause the output spill to auto-shrink. The exact manner of auto-shrinking also depends on which "output_style" is utilized in the fourth parameter describe at section D above (see attached XL2BB examples).
Note that the numbers included in the array brackets for excluding types don't have to be in any order, as shown in the example above
Note that the same principles specified for exclude_types apply to the include_types parameter as described below in section F
F) The sixth parameter, include_types, 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 fifth parameter in section E 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) may also cause the main output spill to auto-shrink. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples).
G) The seventh parameter, replace_with, 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
Note that if you use the replace_with parameter by itself, i.e. without using one of exclude_types or include_types, the function will crash with a single #N/A
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that if replace_with is used, the function output will not auto-shrink relative to the unexcluded/unincluded spill. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples)
H) The eighth parameter, if_empty, 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 if_empty parameter, this entry will not be shown
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that the "empty" status of the output as well as the use of if_empty, if supplied, is controlled by my JAI module described below (I have adopted the JXX naming pattern for my modules and helper functions as there are the fewest number of built-in Excel functions starting with the letter J)
PROCESS
XERROR.TYPE (posted separately)
XTYPE (posted separately)
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)
JAF (module for 2D "exclude_types/include_types/replace_with" when spill includes non-core blank cells)
JAH (removes rows and columns containing non-core blank cells)
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])
JAR (module for 2D "exclude_types/include_types/replace_with" removing all blank cells generated by the exclusion/inclusion)
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 capable of being evaluated logically or more than a single entry in array brackets "{}"]))
IO (shortened form of ISOMITTED)
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)
AllTypes
Here are all the modules in the XL2BB format for easier transfer (note that these are the most up-to-date versions compared with the same modules from my previous posts for other functions that may contain older versions):
General demo, with "size" for rows:
PROCESS was developed primarily to enable resizing of the input reference into an array/range of any desired size, i.e. rows(s) and/or column(s). When the output is chosen to be two-dimensional (which is the main reason I developed the function), PROCESS can be considered as the reverse of LIN/TOROW/TOCOL; of course, the function has full flexibility for output size/dimension(s) which means that the output can be chosen to be linear, if needed, though for this purpose, LIN would be a much better choice as it provides several options for controlling the output linear array's style.
You control the output dimension(s) by specifying the "size" of one of the dimensions (i.e. either rows or columns) of the desired output array/range, and the function will automatically determine the minimum "size" of the other dimension. Note that depending on the size of the input and the size of the desired output dimension(s), the function may append one or more blank cells at the end of the array/range (towards the bottom/right) (see the first two attached XL2BB examples); I refer to these blank cells as non-core blank cells. These non-core blank cells are highlighted in gray in the provided examples, and they will never occur in more than one row in the output (i.e. they may only occur as one partial row). Note that if you specify a "size" that will result in a spill of more rows than needed to accommodate the spill, the function will automatically exclude the extra rows and generate the minimum-size spill necessary (which is the reason why non-core blank cells will never exceed one row).
Furthermore, instead of (or in addition to) determining the output dimensions of the array, you can utilize PROCESS to have full control over data type(s) inclusion/exclusion in the output spill (hence the name "process"). For this purpose, simply leave the "size" parameter unused; note that if you don't use the "size" parameter, the output spill will have the same general dimensions of the input which may, of course, auto-shrink depending on a) what data type(s) are included/excluded and b) on the "output_style"; see the parameter descriptions below. This particular way of utilizing PROCESS could be very useful if you need to prepare (i.e. "process") the input data in order to be used with any of the built-in Excel functions without having to go through the hassle of controlling data type(s) inclusion/exclusion within those functions (by writing additional code) given that Excel's built-in functions do not have this kind of straightforward/full-resolution control over data types (some built-in functions have basic data exclusion feature such as "ignore blanks" or "ignore errors", but that is rudimentary compared with the "exclude_types/include_types/replace_with" system that I have developed). Note that most of the functions that I have developed have this kind of full-resolution data "processing" control for inclusion/exclusion already built into them, so you don't need to use PROCESS with/for them.
PROCESS takes eight parameters, one required, and seven optional, as follows:
A) The first parameter, reference, required, specifies the function input and can be a cell, range, or array
Note that if none of the other parameters are utilized, the function will spill the input reference exactly in its original form
B) The second parameter, size, specifies the number of the output rows or columns and takes a single number (integer greater than or equal to 1) as argument
Note that whether "size" refers to rows or columns is controlled by the next (i.e. third) parameter, "columns", as described below
Note that if more than one number is entered through the array brackets "{}", or if any other "type" of element(s) is entered, the function will return a single #N/A
C) The third parameter, columns, optional, specifies whether the second parameter, i.e. "size", applies to rows or columns and takes two general arguments:
0 or omitted or FALSE, for "off" meaning that the "size" specified in the second parameter refers to rows
1 or TRUE or any number other than 0, for "on" meaning that the "size" specified in the second parameter refers to columns
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
Note that if this parameter is used without using the second parameter, the function will return a single #N/A
Note that if more than one entry is included in this parameter via the array brackets "{}", the function will crash with a single #N/A
D) The fourth parameter, output_style, optional, specifies how the blank cells generated through data exclusion/inclusion are handled and takes two arguments:
0 or omitted, for removing all blank cells, if any, generated through data exclusion/inclusion
1, for removing only full rows or columns, if any, of blank cells generated through data exclusion/inclusion
Note that this parameter takes effect only if the "exclude_types" or "include_types" parameters is used and without the "replace_with" parameter (see below for descriptions). If no cells are excluded/included or if the excluded/included cells are replaced with other data through the "replace_with" parameter, then "output_style" being omitted, 0, or 1 will result in the same output spill.
Note that argument 0 or omitted attempts to be faithful to the specified "size" and it simply joins the output cells together without preserving the data structure of the unexcluded/unincluded spill, which means that cells that were in different rows of the unexcluded/unincluded spill may now appear on the same row of the excluded/included output (see attached XL2BB examples). However, argument 1 attempts to be faithful to the data structure of the unexcluded/unincluded spill, which is why it removes only full rows/columns of blank cells generated through data exclusion/inclusion. This means that the excluded/included output may not necessarily have the specified "size". Thus, the decision as to which argument to use depends on whether the "size" is more important for the particular usage or the data "structure" of the unexcluded/unincluded spill. Of course, the overall linear order of the input cells, nonetheless, is still preserved with either argument (i.e. no scrambling occurs).
Note that both arguments 0 or omitted and 1 may cause the output spill to auto-shrink. For argument 0, the auto-shrink happens at the bottom right corner of the output relative to the unexcluded/unincluded spill. For argument 1, the auto-shrink may happen for rows (upward auto-shrink) and/or columns (leftward auto-shrink) of the output relative to the unexcluded/unincluded spill (see attached XL2BB examples).
E) The fifth parameter, exclude_types, 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,31} for excluding numbers and real blanks, respectively, or {16,2,4} for excluding errors, texts, and logicals, respectively; 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 excluding type(s) may cause the output spill to auto-shrink. The exact manner of auto-shrinking also depends on which "output_style" is utilized in the fourth parameter describe at section D above (see attached XL2BB examples).
Note that the numbers included in the array brackets for excluding types don't have to be in any order, as shown in the example above
Note that the same principles specified for exclude_types apply to the include_types parameter as described below in section F
F) The sixth parameter, include_types, 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 fifth parameter in section E 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) may also cause the main output spill to auto-shrink. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples).
G) The seventh parameter, replace_with, 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
Note that if you use the replace_with parameter by itself, i.e. without using one of exclude_types or include_types, the function will crash with a single #N/A
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that if replace_with is used, the function output will not auto-shrink relative to the unexcluded/unincluded spill. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples)
H) The eighth parameter, if_empty, 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 if_empty parameter, this entry will not be shown
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that the "empty" status of the output as well as the use of if_empty, if supplied, is controlled by my JAI module described below (I have adopted the JXX naming pattern for my modules and helper functions as there are the fewest number of built-in Excel functions starting with the letter J)
PROCESS
VBA Code:
=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],
LET(
r,IF(reference="","",reference),
x,TOROW(r),
y,COLUMNS(x),
l,PLS(MIN(size,y)),
w,XTYPE(reference,3),
c,PLSL(columns),
o,PLS(PO0(output_style)),
d,ROUNDUP(y/l,0),
z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),
a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),
m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),
n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),
p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),
IF(
OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),
NA(),
JAI(
SWITCH(JAL(exclude_types,include_types,replace_with),
0,m,
1,SWITCH(o,
0,JAR(exclude_types,include_types,replace_with,m,p),
1,JAF(n,exclude_types,include_types,replace_with,m,p))),
if_empty
)
)
)
)XERROR.TYPE (posted separately)
XTYPE (posted separately)
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,
IF(SUM(T1F0(ISERROR(MATCH(types,AllTypes,0)))),
NA(),
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)))
)
)
)JAF (module for 2D "exclude_types/include_types/replace_with" when spill includes non-core blank cells)
VBA Code:
=LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,
LET(
c,IF(IO(include_types),exclude_types,include_types),
w,IF(ISNUMBER(core_output),1,0),
x,JAB(c,advanced_types_array),
m,IF(
IO(include_types),
IF(w*x,IF(IO(replace_with),"",replace_with),main_array),
IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),
IF(IO(replace_with),JAH(core_output,x,m,include_types),m)
)
)JAH (removes rows and columns containing non-core blank cells)
VBA Code:
=LAMBDA(core_cells,treated_cells,treated_output,include_types,
LET(
c,core_cells,
t,treated_cells,
m,IF(ISNUMBER(c),1,0),
d,IF(IF(IO(include_types),t*m,t),1,0),
i,IF(IO(include_types),m-d,d),
e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),
o,FILTER(e,e),
f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),
p,FILTER(f,f),
CHOOSECOLS(CHOOSEROWS(treated_output,o),p)
)
)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:
=LAMBDA(reference,
IF(
IO(reference),
FALSE,
IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)
)
)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)
)
)JAR (module for 2D "exclude_types/include_types/replace_with" removing all blank cells generated by the exclusion/inclusion)
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,
LET(
c,IF(IO(include_types),exclude_types,include_types),
w,IF(advanced_types_array="","",JAB(c,advanced_types_array)),
x,LET(
a,IF(IO(include_types),IFERROR(IF(w,0,1),""),w),
IF(a=0,"",a)),
y,LET(
b,COUNT(x),
a,SEQUENCE(ROUNDUP(b/COLUMNS(x),0),MIN(b,COLUMNS(x))),
IF(a>b,"",a)),
z,IF(
IO(replace_with),
FILTER(TOROW(main_array),TOROW(x)<>""),
IF((x="")*(ISNUMBER(w)),replace_with,main_array)),
IF(
IO(replace_with),
IF(ISNUMBER(y),INDEX(z,1,y),y),
z
)
)
)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 capable of being evaluated logically 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))
)
)
)
)IO (shortened form of ISOMITTED)
VBA Code:
=LAMBDA(parameter,
ISOMITTED(parameter)
)NIO [shortened form of NOT(ISOMITTED)]
VBA Code:
=LAMBDA(parameter,
NOT(ISOMITTED(parameter))
)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())
)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}Here are all the modules in the XL2BB format for easier transfer (note that these are the most up-to-date versions compared with the same modules from my previous posts for other functions that may contain older versions):
| PROCESS.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 | |||
| 1 | |||||||||||||||||||||||||||||
| 2 | PROCESS | #CALC! | |||||||||||||||||||||||||||
| 3 | XTYPE | #CALC! | |||||||||||||||||||||||||||
| 4 | XERROR.TYPE | #CALC! | |||||||||||||||||||||||||||
| 5 | IO | #CALC! | |||||||||||||||||||||||||||
| 6 | JAB | #CALC! | |||||||||||||||||||||||||||
| 7 | JAF | #CALC! | |||||||||||||||||||||||||||
| 8 | JAH | #CALC! | |||||||||||||||||||||||||||
| 9 | JAI | #CALC! | |||||||||||||||||||||||||||
| 10 | JAL | #CALC! | |||||||||||||||||||||||||||
| 11 | JAN | #CALC! | |||||||||||||||||||||||||||
| 12 | JAO | #CALC! | |||||||||||||||||||||||||||
| 13 | JAP | #CALC! | |||||||||||||||||||||||||||
| 14 | JAR | #CALC! | |||||||||||||||||||||||||||
| 15 | NIO | #CALC! | |||||||||||||||||||||||||||
| 16 | PLS | #CALC! | |||||||||||||||||||||||||||
| 17 | PLSL | #CALC! | |||||||||||||||||||||||||||
| 18 | PO0 | #CALC! | |||||||||||||||||||||||||||
| 19 | T1F0 | #CALC! | |||||||||||||||||||||||||||
| 20 | AllTypes | 1 | 2 | 3 | 4 | 16 | 31 | 32 | 41 | 42 | 160 | 161 | 162 | 163 | 164 | 165 | 166 | 167 | 168 | 169 | 1610 | 1611 | 1612 | 1613 | 1614 | ||||
| 21 | |||||||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),x,TOROW(r),y,COLUMNS(x),l,PLS(MIN(size,y)),w,XTYPE(reference,3),c,PLSL(columns),o,PLS(PO0(output_style)),d,ROUNDUP(y/l,0),z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),IF(OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),NA(),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,m,1,SWITCH(o,0,JAR(exclude_types,include_types,replace_with,m,p),1,JAF(n,exclude_types,include_types,replace_with,m,p))),if_empty)))) |
| C3 | C3 | =LAMBDA(reference,[analysis_type],IF(IO(reference),NA(),LET(r,reference,a,PLS(PO0(analysis_type)),IF(OR(a={0,1,2,3}),LET(z,MAP(r,LAMBDA(b,IF(ISERROR(b),XERROR.TYPE(b),IF(b="",IF(ISBLANK(b),31,32),IF(ISNUMBER(b),1,IF(ISTEXT(b),2,IF(b,41,42))))))),SWITCH(a,0,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK",32,"dt:BLANK",41,"dt:LOGICAL",42,"dt:LOGICAL","dt:ERROR"),1,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_Data",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!"),2,SWITCH(z,1,1,2,2,31,3,32,3,41,4,42,4,16),3,z)),LET(t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),SWITCH(a,4,t,5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,t)))))) |
| C4 | C4 | =LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(JAN(if_not_error),NA(),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o))))))) |
| C5 | C5 | =LAMBDA(parameter,ISOMITTED(parameter)) |
| C6 | C6 | =LAMBDA(types,advanced_types_array,IF(SUM(T1F0(ISERROR(MATCH(types,AllTypes,0)))),NA(),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)))))) |
| C7 | C7 | =LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(c,IF(IO(include_types),exclude_types,include_types),w,IF(ISNUMBER(core_output),1,0),x,JAB(c,advanced_types_array),m,IF(IO(include_types),IF(w*x,IF(IO(replace_with),"",replace_with),main_array),IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),IF(IO(replace_with),JAH(core_output,x,m,include_types),m))) |
| C8 | C8 | =LAMBDA(core_cells,treated_cells,treated_output,include_types,LET(c,core_cells,t,treated_cells,m,IF(ISNUMBER(c),1,0),d,IF(IF(IO(include_types),t*m,t),1,0),i,IF(IO(include_types),m-d,d),e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),o,FILTER(e,e),f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),p,FILTER(f,f),CHOOSECOLS(CHOOSEROWS(treated_output,o),p))) |
| C9 | C9 | =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)))) |
| C10 | C10 | =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))) |
| C11 | C11 | =LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)))) |
| C12 | C12 | =LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE))) |
| C13 | C13 | =LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE))) |
| C14 | C14 | =LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(c,IF(IO(include_types),exclude_types,include_types),w,IF(advanced_types_array="","",JAB(c,advanced_types_array)),x,LET(a,IF(IO(include_types),IFERROR(IF(w,0,1),""),w),IF(a=0,"",a)),y,LET(b,COUNT(x),a,SEQUENCE(ROUNDUP(b/COLUMNS(x),0),MIN(b,COLUMNS(x))),IF(a>b,"",a)),z,IF(IO(replace_with),FILTER(TOROW(main_array),TOROW(x)<>""),IF((x="")*(ISNUMBER(w)),replace_with,main_array)),IF(IO(replace_with),IF(ISNUMBER(y),INDEX(z,1,y),y),z))) |
| C15 | C15 | =LAMBDA(parameter,NOT(ISOMITTED(parameter))) |
| C16 | C16 | =LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1)) |
| C17 | C17 | =LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p)))))) |
| C18 | C18 | =LAMBDA([parameter],IF(IO(parameter),0,parameter)) |
| C19 | C19 | =LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA())) |
| C20:Z20 | C20 | ={1,2,3,4,16,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614} |
| Dynamic array formulas. | ||
General demo, with "size" for rows:
| PROCESS.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 | |||
| 1 | ||||||||||||||||||||||||||||
| 2 | #CALC! | |||||||||||||||||||||||||||
| 3 | ||||||||||||||||||||||||||||
| 4 | 53 | book | TRUE | 32 | #DIV/0! | a | 67 | |||||||||||||||||||||
| 5 | 23.67 | 78 | notebook | TRUE | 0.543 | b | ||||||||||||||||||||||
| 6 | #CALC! | 17 | FALSE | #REF! | ||||||||||||||||||||||||
| 7 | ||||||||||||||||||||||||||||
| 8 | ||||||||||||||||||||||||||||
| 9 | length | columns | output_style | output | ||||||||||||||||||||||||
| 10 | 1 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | #DIV/0! | a | 67 | 23.67 | 78 | notebook | TRUE | 0.543 | b | #CALC! | 17 | FALSE | #REF! | ||||||||
| 11 | 2 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | #DIV/0! | a | 67 | 23.67 | 78 | notebook | |||||||||||||||
| 12 | TRUE | 0.543 | b | #CALC! | 17 | FALSE | #REF! | |||||||||||||||||||||
| 13 | 3 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | #DIV/0! | a | 67 | ||||||||||||||||||
| 14 | 23.67 | 78 | notebook | TRUE | 0.543 | b | ||||||||||||||||||||||
| 15 | #CALC! | 17 | FALSE | #REF! | ||||||||||||||||||||||||
| 16 | 4 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | #DIV/0! | a | |||||||||||||||||||
| 17 | 67 | 23.67 | 78 | notebook | TRUE | |||||||||||||||||||||||
| 18 | 0.543 | b | #CALC! | 17 | FALSE | |||||||||||||||||||||||
| 19 | #REF! | |||||||||||||||||||||||||||
| 20 | 5 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | #DIV/0! | ||||||||||||||||||||
| 21 | a | 67 | 23.67 | 78 | ||||||||||||||||||||||||
| 22 | notebook | TRUE | 0.543 | b | #CALC! | |||||||||||||||||||||||
| 23 | 17 | FALSE | #REF! | |||||||||||||||||||||||||
| 24 | ||||||||||||||||||||||||||||
| 25 | 6 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 32 | |||||||||||||||||||||
| 26 | #DIV/0! | a | 67 | 23.67 | ||||||||||||||||||||||||
| 27 | 78 | notebook | TRUE | |||||||||||||||||||||||||
| 28 | 0.543 | b | #CALC! | |||||||||||||||||||||||||
| 29 | 17 | FALSE | #REF! | |||||||||||||||||||||||||
| 30 | ||||||||||||||||||||||||||||
| 31 | 7 | omitted | omitted or 0 or 1 | 53 | book | TRUE | ||||||||||||||||||||||
| 32 | 32 | #DIV/0! | a | |||||||||||||||||||||||||
| 33 | 67 | 23.67 | 78 | |||||||||||||||||||||||||
| 34 | notebook | TRUE | ||||||||||||||||||||||||||
| 35 | 0.543 | b | #CALC! | |||||||||||||||||||||||||
| 36 | 17 | FALSE | ||||||||||||||||||||||||||
| 37 | #REF! | |||||||||||||||||||||||||||
| 38 | 8 or 9 or 10 | omitted | omitted or 0 or 1 | 53 | book | TRUE | 53 | book | TRUE | 53 | book | TRUE | ||||||||||||||||
| 39 | 32 | #DIV/0! | a | 32 | #DIV/0! | a | 32 | #DIV/0! | a | |||||||||||||||||||
| 40 | 67 | 23.67 | 78 | 67 | 23.67 | 78 | 67 | 23.67 | 78 | |||||||||||||||||||
| 41 | notebook | TRUE | notebook | TRUE | notebook | TRUE | ||||||||||||||||||||||
| 42 | 0.543 | b | #CALC! | 0.543 | b | #CALC! | 0.543 | b | #CALC! | |||||||||||||||||||
| 43 | 17 | FALSE | 17 | FALSE | 17 | FALSE | ||||||||||||||||||||||
| 44 | #REF! | #REF! | #REF! | |||||||||||||||||||||||||
| 45 | 11 or 12 or 13 or 14 or 15 or 16 or 17 or 18 or 19 or 20 | omitted | omitted or 0 or 1 | 53 | book | 53 | book | 53 | book | |||||||||||||||||||
| 46 | TRUE | 32 | TRUE | 32 | TRUE | 32 | ||||||||||||||||||||||
| 47 | #DIV/0! | a | #DIV/0! | a | #DIV/0! | a | ||||||||||||||||||||||
| 48 | 67 | 23.67 | 67 | 23.67 | 67 | 23.67 | ||||||||||||||||||||||
| 49 | 78 | 78 | 78 | |||||||||||||||||||||||||
| 50 | notebook | TRUE | notebook | TRUE | notebook | TRUE | ||||||||||||||||||||||
| 51 | 0.543 | b | 0.543 | b | 0.543 | b | ||||||||||||||||||||||
| 52 | #CALC! | #CALC! | #CALC! | |||||||||||||||||||||||||
| 53 | 17 | FALSE | 17 | FALSE | 17 | FALSE | ||||||||||||||||||||||
| 54 | #REF! | #REF! | #REF! | |||||||||||||||||||||||||
| 55 | ||||||||||||||||||||||||||||
| 56 | 21 or greater | omitted | omitted or 0 or 1 | 53 | 53 | 53 | 53 | 53 | 53 | 53 | 53 | 53 | ||||||||||||||||
| 57 | book | book | book | book | book | book | book | book | book | |||||||||||||||||||
| 58 | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | |||||||||||||||||||
| 59 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | |||||||||||||||||||
| 60 | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |||||||||||||||||||
| 61 | a | a | a | a | a | a | a | a | a | |||||||||||||||||||
| 62 | 67 | 67 | 67 | 67 | 67 | 67 | 67 | 67 | 67 | |||||||||||||||||||
| 63 | 23.67 | 23.67 | 23.67 | 23.67 | 23.67 | 23.67 | 23.67 | 23.67 | 23.67 | |||||||||||||||||||
| 64 | 78 | 78 | 78 | 78 | 78 | 78 | 78 | 78 | 78 | |||||||||||||||||||
| 65 | ||||||||||||||||||||||||||||
| 66 | notebook | notebook | notebook | notebook | notebook | notebook | notebook | notebook | notebook | |||||||||||||||||||
| 67 | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | |||||||||||||||||||
| 68 | 0.543 | 0.543 | 0.543 | 0.543 | 0.543 | 0.543 | 0.543 | 0.543 | 0.543 | |||||||||||||||||||
| 69 | b | b | b | b | b | b | b | b | b | |||||||||||||||||||
| 70 | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | #CALC! | |||||||||||||||||||
| 71 | ||||||||||||||||||||||||||||
| 72 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | |||||||||||||||||||
| 73 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | |||||||||||||||||||
| 74 | ||||||||||||||||||||||||||||
| 75 | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | |||||||||||||||||||
| 76 | ||||||||||||||||||||||||||||
| 77 | ||||||||||||||||||||||||||||
res-r | ||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),x,TOROW(r),y,COLUMNS(x),l,PLS(MIN(size,y)),w,XTYPE(reference,3),c,PLSL(columns),o,PLS(PO0(output_style)),d,ROUNDUP(y/l,0),z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),IF(OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),NA(),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,m,1,SWITCH(o,0,JAR(exclude_types,include_types,replace_with,m,p),1,JAF(n,exclude_types,include_types,replace_with,m,p))),if_empty)))) |
| F4 | F4 | =1/0 |
| C6 | C6 | =IF(1,"") |
| E10:Y10 | E10 | =PROCESS($B$4:$H$6,1) |
| E11:O12 | E11 | =PROCESS($B$4:$H$6,2) |
| E13:K15 | E13 | =PROCESS($B$4:$H$6,3) |
| E16:J19 | E16 | =PROCESS($B$4:$H$6,4) |
| E20:I24 | E20 | =PROCESS($B$4:$H$6,5) |
| E25:H30 | E25 | =PROCESS($B$4:$H$6,6) |
| E31:G37 | E31 | =PROCESS($B$4:$H$6,7) |
| E38:G44 | E38 | =PROCESS($B$4:$H$6,8) |
| H38:J44 | H38 | =PROCESS($B$4:$H$6,9) |
| K38:M44 | K38 | =PROCESS($B$4:$H$6,10) |
| E45:F55 | E45 | =PROCESS($B$4:$H$6,11) |
| G45:H55 | G45 | =PROCESS($B$4:$H$6,15) |
| I45:J55 | I45 | =PROCESS($B$4:$H$6,20) |
| E56:E76 | E56 | =PROCESS($B$4:$H$6,21) |
| F56:F76 | F56 | =PROCESS($B$4:$H$6,22) |
| G56:G76 | G56 | =PROCESS($B$4:$H$6,30) |
| I56:I76 | I56 | =PROCESS($B$4:$H$6,21,,0) |
| J56:J76 | J56 | =PROCESS($B$4:$H$6,22,,0) |
| K56:K76 | K56 | =PROCESS($B$4:$H$6,30,,0) |
| M56:M76 | M56 | =PROCESS($B$4:$H$6,21,,1) |
| N56:N76 | N56 | =PROCESS($B$4:$H$6,22,,1) |
| O56:O76 | O56 | =PROCESS($B$4:$H$6,30,,1) |
| Dynamic array formulas. | ||
Upvote
0
