Last active
December 21, 2025 12:19
-
-
Save RackofLambda/11bbff6441327831cde24aef32cb6ef9 to your computer and use it in GitHub Desktop.
A collection of Microsoft Excel LAMBDA functions for transforming tables and arrays.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* List of LAMBDA functions in this collection: | |
| -------------------------------------------- | |
| CALENDARRAY ---->> Generates a monthly calendar for the specified number of months. | |
| CROSSJOIN ------>> Returns the Cartesian product of two tables or arrays. | |
| CROSSJOINM ----->> Returns the Cartesian product of multiple arrays. | |
| KRON ----------->> Returns the Kronecker product of two arrays. | |
| PERMA ---------->> Returns all permutations or combinations, with or without repetitions, for a given number of items. | |
| REPTA ---------->> Repeats a range or array vertically or horizontally a specified number of times. | |
| REPTROWS ------->> Repeats each row in a range or array a specified number of times. | |
| REPTCOLS ------->> Repeats each column in a range or array a specified number of times. | |
| REPTRWCL ------->> Repeats each row or column in a range or array a specified number of times. | |
| REPTINTV ------->> Repeats each row in a range or array based on the number of intervals between two values. | |
| FLIP ----------->> Reverses the order of rows and/or columns in a range or array. | |
| ROTATE --------->> Rotates a range or array to the left or right by 90 degrees per rotation. | |
| FLATTENBLOCKS -->> Reshapes a range or array by transforming blocks of arbitrary size into rows or columns. | |
| HWRAP ---------->> Wraps a table or array horizontally a specified number of times and/or after a specified number of rows. | |
| VWRAP ---------->> Wraps a range or array vertically a specified number of times and/or after a specified number of columns. | |
| UNPIVOT -------->> Rotates a table by transforming columns into rows, automatically removing blanks and errors. | |
| UNPIVOT2 ------->> Rotates a table by transforming columns into rows, removing blanks and/or errors as specified. | |
| SORTBYROW ------>> Sorts the contents of each row in a range or array independently from one another. | |
| SORTBYCOL ------>> Sorts the contents of each column in a range or array independently from one another. | |
| SORTBYRC ------->> Sorts the contents of each row or column in a range or array independently from one another. | |
| RANDOMIZE ------>> Randomizes the values, rows or columns in a range or array. | |
| TEXTTOCOLS ----->> Splits a single column of delimited text into multiple columns. | |
| TEXTTOCOLS2 ---->> Splits a single column of delimited text into multiple columns (for use with TOCOL/TOROW to ignore errors). | |
| Nz ------------->> Returns a zero-length string or another specified value when a variant is null (derived from a blank cell in a range). | |
| */ | |
| /* FUNCTION NAME: CALENDARRAY | |
| DESCRIPTION: Generates a monthly calendar for the specified number of months. | |
| SYNTAX: =CALENDARRAY(start_year,[start_month],[months],[wrap_count],[serial_mode],[week_type],[mask_with]) | |
| ARGUMENTS: start_year (required) - the starting year of the calendar. | |
| [start_month] (optional) - the starting month of the calendar (default is 1). | |
| [months] (optional) - the number of months to be included in the calendar (if [start_month] is omitted, default is 12; otherwise, default is 1). | |
| [wrap_count] (optional) - the number of times the calendar is wrapped horizontally (if both [start_month] and [months] are omitted, default is 3; otherwise, default is 1). | |
| [serial_mode] (Boolean, optional) - specifies the output mode. | |
| 0 or FALSE - returns days of the month (default) | |
| 1 or TRUE - returns date serial numbers | |
| [week_type] (integer, optional) - specifies the starting day of the week. | |
| 1 - Monday to Sunday | |
| 2 - Tuesday to Monday | |
| 3 - Wednesday to Tuesday | |
| 4 - Thursday to Wednesday | |
| 5 - Friday to Thursday | |
| 6 - Saturday to Friday | |
| 7 - Sunday to Saturday (default) | |
| [mask_with] (variant, optional) - the value (scalar) or text format (single element array) with which to mask days in the preceding or succeeding months. | |
| EXAMPLES: =CALENDARRAY(2025) | |
| =CALENDARRAY(2025,9) | |
| =CALENDARRAY(2025,,24,3) | |
| =CALENDARRAY(2025,,60,12,,,"") | |
| =CALENDARRAY(2025,,24,3,TRUE,1,"") | |
| =CALENDARRAY(2025,,24,3,1,,{"m|dd"}) | |
| */ | |
| CALENDARRAY = LAMBDA(start_year,[start_month],[months],[wrap_count],[serial_mode],[week_type],[mask_with], | |
| LET( | |
| x, IF(ISOMITTED(wrap_count), IF(AND(ISOMITTED(start_month), ISOMITTED(months)), 3, 1), wrap_count), | |
| y, IF(ISOMITTED(months), IF(ISOMITTED(start_month), 12, 1), months), | |
| n, DATE(start_year, ISOMITTED(start_month) + start_month + SEQUENCE(CEILING.MATH(y, x),, 0), 1), | |
| m, n - WEEKDAY(n, 10 + IF(ISOMITTED(week_type), 7, week_type)) + SEQUENCE(, 42), | |
| v, IF(serial_mode, m, DAY(m)), | |
| a, HSTACK( | |
| EXPAND("", ROWS(n), 3, ""), | |
| EXPAND(TEXT(n, "mmm yyyy"),, 4, ""), | |
| IFNA(TEXT(SEQUENCE(, 7, DATE(1905, 1, 1) + week_type), "ddd"), n), | |
| IF(ISOMITTED(mask_with), v, IF(MONTH(m) = MONTH(n), v, IF(TYPE(mask_with) = 64, TEXT(m, mask_with), mask_with))) | |
| ), | |
| p, SORTBY(EXPAND(a,, 72, ""), HSTACK(QUOTIENT(SEQUENCE(, 63, 0), 7), SEQUENCE(, 9, 0))), | |
| k, x * 8, | |
| i, SEQUENCE(COUNTA(p) / k,, 0), | |
| j, SEQUENCE(, k, 0), | |
| DROP(INDEX(p, 1 + QUOTIENT(j, 8) + QUOTIENT(i, 9) * x, 1 + MOD(j, 8) + MOD(i, 9) * 8), -1, -1) | |
| ) | |
| ); | |
| /* FUNCTION NAME: CROSSJOIN | |
| DESCRIPTION: Returns the Cartesian product of two tables or arrays. | |
| SYNTAX: =CROSSJOIN(array1,array2,[order_by_array2],[headers]) | |
| ARGUMENTS: array1, array2 (required) - the tables or arrays to be joined. | |
| [order_by_array2] (Boolean, optional) - specifies the output order. | |
| 0 or FALSE - order by array1 (default) | |
| 1 or TRUE - order by array2 | |
| [headers] (integer, optional) - specifies whether the tables have headers and whether headers should be returned in the results. | |
| 0 - No (default) | |
| 1 - Yes but don't show | |
| 2 - No but generate | |
| 3 - Yes and show | |
| EXAMPLES: =CROSSJOIN(Table1,Table2,TRUE) | |
| =CROSSJOIN(Table1,Table2,,2) | |
| =CROSSJOIN(Table1[#All],Table2[#All],,1) | |
| =CROSSJOIN(Table1[#All],Table2[#All],1,3) | |
| */ | |
| CROSSJOIN = LAMBDA(array1,array2,[order_by_array2],[headers], | |
| LET( | |
| inc, CHOOSE(headers + 1, 0, 1, 0, 1), | |
| shw, CHOOSE(headers + 1, 0, 0, 1, 1), | |
| a, DROP(array1, inc), | |
| b, DROP(array2, inc), | |
| i, SEQUENCE(ROWS(a)), | |
| j, SEQUENCE(, ROWS(b)), | |
| v, HSTACK( | |
| CHOOSEROWS(a, TOCOL(IF(j, i),, order_by_array2)), | |
| CHOOSEROWS(b, TOCOL(IF(i, j),, order_by_array2)) | |
| ), | |
| IF( | |
| shw, | |
| VSTACK( | |
| IF( | |
| inc, | |
| HSTACK(TAKE(array1, 1), TAKE(array2, 1)), | |
| HSTACK("tbl1.Col" & SEQUENCE(, COLUMNS(a)), "tbl2.Col" & SEQUENCE(, COLUMNS(b))) | |
| ), | |
| v | |
| ), | |
| v | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: CROSSJOINM | |
| DESCRIPTION: Returns the Cartesian product of multiple arrays. | |
| PREREQUISITES: CROSSJOIN | |
| SYNTAX: =CROSSJOINM(array1,array2,[array3],[array4],[array5],[array6],[array7],[array8]) | |
| ARGUMENTS: array1, array2, [array3], ... - array1 and array2 are required; subsequent arrays are optional (a minimum of two arrays are required). | |
| EXAMPLES: =CROSSJOINM(Table1,Table2,Table3) | |
| =CROSSJOINM(A2:B8,D2:F6,H2:H10,J2:K4,M2:M5,O2:O3) | |
| */ | |
| CROSSJOINM = LAMBDA(array1,array2,[array3],[array4],[array5],[array6],[array7],[array8], | |
| LET( | |
| arg, VSTACK(LAMBDA(array1), LAMBDA(array2), LAMBDA(array3), LAMBDA(array4), LAMBDA(array5), LAMBDA(array6), LAMBDA(array7), LAMBDA(array8)), | |
| arr, FILTER(arg, NOT(MAP(arg, LAMBDA(x, ISOMITTED(x()))))), | |
| REDUCE(INDEX(arr, 1, 1)(), DROP(arr, 1), LAMBDA(a,v, CROSSJOIN(a, v()))) | |
| ) | |
| ); | |
| /* FUNCTION NAME: KRON | |
| DESCRIPTION: Returns the Kronecker product of two arrays. | |
| SYNTAX: =KRON(array1,array2,[function]) | |
| ARGUMENTS: array1, array2 (required) - the arrays to be evaluated. | |
| [function] (optional) - a custom LAMBDA function with two parameters or an eta-reduced LAMBDA (default is PRODUCT). | |
| EXAMPLES: =KRON(A2:C3,E2:H5) | |
| =KRON(A2:C3,E2:H5,SUM) | |
| =KRON(A2:C3,E2:H5,LAMBDA(a,b,TEXTJOIN("|",0,a,b))) | |
| */ | |
| KRON = LAMBDA(array1,array2,[function], | |
| LET( | |
| i, SEQUENCE(ROWS(array1)), | |
| j, SEQUENCE(, ROWS(array2)), | |
| m, SEQUENCE(COLUMNS(array1)), | |
| n, SEQUENCE(, COLUMNS(array2)), | |
| a, INDEX(array1, TOCOL(IF(j, i)), TOROW(IF(n, m))), | |
| b, INDEX(array2, TOCOL(IF(i, j)), TOROW(IF(m, n))), | |
| IF(ISOMITTED(function), a * b, MAP(a,b, function)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: PERMA | |
| DESCRIPTION: Returns all permutations or combinations, with or without repetitions, for a given number of items. | |
| SYNTAX: =PERMA(number,number_chosen,[combinations],[without_repetitions]) | |
| ARGUMENTS: number (required) - the number of distinct objects (n). | |
| number_chosen (required) - the sample size (r). | |
| [combinations] (Boolean, optional) - specifies whether or not order matters. | |
| 0 or FALSE - permutations (default) | |
| 1 or TRUE - combinations | |
| [without_repetitions] (Boolean, optional) - specifies whether or not replacements are allowed. | |
| 0 or FALSE - with repetitions (default) | |
| 1 or TRUE - without repetitions | |
| LIMITATIONS: n r n^r w/o | |
| ----------------------------- | |
| 1024 2 1,048,576 T | |
| 101 3 1,030,301 T | |
| 32 4 1,048,576 T | |
| 16 5 1,048,576 T | |
| 10 6 1,000,000 T | |
| 7 7 823,543 T | |
| 5 8 390,625 F | |
| 4 10 1,048,576 F | |
| 3 12 531,441 F | |
| 2 20 1,048,576 F | |
| EXAMPLES: =PERMA(1000,2) | |
| =PERMA(25,3,TRUE) | |
| =PERMA(10,4,1,1) | |
| =INDEX(A2:A7,PERMA(ROWS(A2:A7),ROWS(A2:A7),,TRUE)) | |
| */ | |
| PERMA = LAMBDA(number,number_chosen,[combinations],[without_repetitions], | |
| IF( | |
| number_chosen = 1, | |
| SEQUENCE(number), | |
| LET( | |
| j, SEQUENCE(, number), | |
| k, REDUCE(TOCOL(j), SEQUENCE(number_chosen - 1), LAMBDA(a,v, | |
| LET(i, SEQUENCE(ROWS(a)), HSTACK(CHOOSEROWS(a, TOCOL(IF(j, i))), TOCOL(IF(i, j)))))), | |
| CHOOSE( | |
| 1 + AND(combinations) + AND(without_repetitions) * 2, | |
| k, | |
| TEXTSPLIT(TEXTAFTER(UNIQUE(BYROW(k, LAMBDA(r, TEXTJOIN("|", 0, "", SORT(r,,, 1))))), "|", SEQUENCE(, number_chosen)), "|"), | |
| FILTER(k, BYROW(k, LAMBDA(r, COLUMNS(UNIQUE(r, 1)) = number_chosen))), | |
| LET( | |
| x, UNIQUE(BYROW(k, LAMBDA(r, TEXTJOIN("|", 0, "", SORT(r,,, 1), COLUMNS(UNIQUE(r, 1)) = number_chosen)))), | |
| TEXTBEFORE(TEXTAFTER(FILTER(x, TEXTAFTER(x, "|", -1)), "|", SEQUENCE(, number_chosen)), "|") | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REPTA | |
| DESCRIPTION: Repeats a range or array vertically or horizontally a specified number of times. | |
| SYNTAX: =REPTA(array,number_times,[horizontal]) | |
| ARGUMENTS: array (required) - the range or array to be repeated. | |
| number_times (required) - the number of repetitions. | |
| [horizontal] (Boolean, optional) - specifies the output direction. | |
| 0 or FALSE - vertical (default) | |
| 1 or TRUE - horizontal | |
| EXAMPLES: =REPTA(A2:C10,3) | |
| =REPTA(A2:C10,5,TRUE) | |
| =REPTA(A2:C10,{4,1,3,2}) | |
| =REPTA(A2:C10,{10,5,15},1) | |
| */ | |
| REPTA = LAMBDA(array,number_times,[horizontal], | |
| IF( | |
| TYPE(number_times) = 64, | |
| LET( | |
| k, TOCOL(number_times), | |
| i, SEQUENCE(, ROWS(array)), | |
| j, SEQUENCE(, COLUMNS(array)), | |
| m, SEQUENCE(MAX(k)), | |
| n, SEQUENCE(ROWS(k)), | |
| IF( | |
| horizontal, | |
| IF(TOCOL(IF(i, k)) >= TOROW(IF(j, m)), INDEX(array, TOCOL(IF(n, i)), TOROW(IF(m, j))), ""), | |
| IF(TOROW(IF(j, k)) >= TOCOL(IF(i, m)), INDEX(array, TOCOL(IF(m, i)), TOROW(IF(n, j))), "") | |
| ) | |
| ), | |
| IF( | |
| horizontal, | |
| CHOOSECOLS(array, TOROW(IF(SEQUENCE(number_times), SEQUENCE(, COLUMNS(array))))), | |
| CHOOSEROWS(array, TOCOL(IF(SEQUENCE(number_times), SEQUENCE(, ROWS(array))))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REPTROWS | |
| DESCRIPTION: Repeats each row in a range or array a specified number of times. | |
| SYNTAX: REPTROWS(array,number_times) | |
| ARGUMENTS: array (required) - the range or array to be repeated. | |
| number_times (required) - the number of repetitions. | |
| EXAMPLES: =REPTROWS(A2:A10,2) | |
| =REPTROWS(A2:C10,3) | |
| =REPTROWS(A2:A10,{5;3;2;1;2;0;2;3;5}) | |
| =REPTROWS(A2:C10,D2:D10) | |
| */ | |
| REPTROWS = LAMBDA(array,number_times, | |
| CHOOSE( | |
| 1 + (COLUMNS(array) = 1) + (TYPE(number_times) = 64) * 2, | |
| CHOOSEROWS(array, TOCOL(IF(SEQUENCE(, number_times), SEQUENCE(ROWS(array))))), | |
| TOCOL(IF(SEQUENCE(, number_times), array)), | |
| CHOOSEROWS(array, TOCOL(IFS(number_times >= SEQUENCE(, MAX(number_times)), SEQUENCE(ROWS(array))), 2)), | |
| TOCOL(IFS(number_times >= SEQUENCE(, MAX(number_times)), array), 2) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REPTCOLS | |
| DESCRIPTION: Repeats each column in a range or array a specified number of times. | |
| SYNTAX: REPTCOLS(array,number_times) | |
| ARGUMENTS: array (required) - the range or array to be repeated. | |
| number_times (required) - the number of repetitions. | |
| EXAMPLES: =REPTCOLS(A2:C2,3) | |
| =REPTCOLS(A2:C10,5) | |
| =REPTCOLS(A2:C2,{3,5,2}) | |
| =REPTCOLS(A2:C10,A1:C1) | |
| */ | |
| REPTCOLS = LAMBDA(array,number_times, | |
| CHOOSE( | |
| 1 + (ROWS(array) = 1) + (TYPE(number_times) = 64) * 2, | |
| CHOOSECOLS(array, TOROW(IF(SEQUENCE(number_times), SEQUENCE(, COLUMNS(array))),, 1)), | |
| TOROW(IF(SEQUENCE(number_times), array),, 1), | |
| CHOOSECOLS(array, TOROW(IFS(number_times >= SEQUENCE(MAX(number_times)), SEQUENCE(, COLUMNS(array))), 2, 1)), | |
| TOROW(IFS(number_times >= SEQUENCE(MAX(number_times)), array), 2, 1) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REPTRWCL | |
| DESCRIPTION: Repeats each row or column in a range or array a specified number of times. | |
| SYNTAX: REPTRWCL(array,number_times,[rept_cols]) | |
| ARGUMENTS: array (required) - the range or array to be repeated. | |
| number_times (required) - the number of repetitions. | |
| [rept_cols] (Boolean, optional) - specifies the items to be repeated. | |
| 0 or FALSE - repeat rows (default) | |
| 1 or TRUE - repeat columns | |
| EXAMPLES: =REPTRWCL(A2:C10,3) | |
| =REPTRWCL(A2:C10,D2:D10) | |
| =REPTRWCL(A2:C10,3,TRUE) | |
| =REPTRWCL(A2:C10,A1:C1,1) | |
| */ | |
| REPTRWCL = LAMBDA(array,number_times,[rept_cols], | |
| CHOOSE( | |
| 1 + AND(rept_cols) + (TYPE(number_times) = 64) * 2, | |
| CHOOSEROWS(array, TOCOL(IF(SEQUENCE(, number_times), SEQUENCE(ROWS(array))))), | |
| CHOOSECOLS(array, TOROW(IF(SEQUENCE(, number_times), SEQUENCE(COLUMNS(array))))), | |
| CHOOSEROWS(array, TOCOL(IFS(TOCOL(number_times) >= SEQUENCE(, MAX(number_times)), SEQUENCE(ROWS(array))), 2)), | |
| CHOOSECOLS(array, TOROW(IFS(TOCOL(number_times) >= SEQUENCE(, MAX(number_times)), SEQUENCE(COLUMNS(array))), 2)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REPTINTV | |
| DESCRIPTION: Repeats each row in a range or array based on the number of intervals between two values. | |
| SYNTAX: REPTINTV(array,start,end,[step],[intv_type]) | |
| ARGUMENTS: array (semi-required) - the range or array to be repeated (if omitted, RowID is generated). | |
| start (semi-required) - the starting value (default is 0). | |
| end (required) - the ending value. | |
| [step] (optional) - the number of intervals between repetitions (default is 1). | |
| [intv_type] (integer, optional) - specifies the interval type. | |
| 1 - Days/numbers (default) | |
| 2 - Months | |
| 3 - Month Ends | |
| EXAMPLES: =REPTINTV(A2:C6,D2:D6,E2:E6) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,2) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,0.5) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,F2:F6) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,F2:F6,2) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,F2:F6,3) | |
| =REPTINTV(A2:C6,D2:D6,E2:E6,F2:F6,G2:G6) | |
| =REPTINTV(,D2:D6,E2:E6,F2:F6,SWITCH(G2:G6,"Months",2,"Month Ends",3,1)) | |
| */ | |
| REPTINTV = LAMBDA(array,start,end,[step],[intv_type], | |
| LET( | |
| a, IF(ISOMITTED(array), SEQUENCE(ROWS(end)), array), | |
| t, IF(ISOMITTED(intv_type), 1, intv_type), | |
| k, IF(ISOMITTED(step), 1, +step), | |
| i, ROWS(a), | |
| s, +start, | |
| e, IF(AND(i > 1, TYPE(start) = 1, TYPE(end) = 1), EXPAND(end, i,, end), end), | |
| n, IF(t = 1, e - s, (YEAR(e) - YEAR(s)) * 12 + MONTH(e) - MONTH(s)), | |
| m, SEQUENCE(, AGGREGATE(14, 6, QUOTIENT(n, k), 1) + 1, 0) * IFS(k > 0, k), | |
| j, CHOOSE(t, s + m, EDATE(s, m), EOMONTH(s, m)), | |
| λ, LAMBDA(x, LAMBDA(y, TOCOL(IFS(x, y), 2)))(e >= j), | |
| HSTACK(IF(COLUMNS(a) = 1, λ(a), CHOOSEROWS(a, λ(SEQUENCE(i)))), λ(j)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: FLIP | |
| DESCRIPTION: Reverses the order of rows and/or columns in a range or array. | |
| SYNTAX: FLIP(array,[vertical],[horizontal],[rotate]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [vertical] (Boolean, optional) - reverses the order of rows (default is TRUE if both [vertical] and [horizontal] are omitted; otherwise, default is FALSE). | |
| [horizontal] (Boolean, optional) - reverses the order of columns (default is TRUE if both [vertical] and [horizontal] are omitted; otherwise, default is FALSE). | |
| [rotate] (Boolean, optional) - transposes the array (performed BEFORE any flip action is taken). | |
| EXAMPLES: =FLIP(SEQUENCE(5,10)) | |
| =FLIP(SEQUENCE(5,10),TRUE) | |
| =FLIP(SEQUENCE(5,10),,TRUE) | |
| =FLIP(SEQUENCE(5,10),,,TRUE) | |
| =FLIP(SEQUENCE(5,10),1,,1) | |
| =FLIP(SEQUENCE(5,10),,1,1) | |
| =FLIP(SEQUENCE(5,10),0,0,1) | |
| */ | |
| FLIP = LAMBDA(array,[vertical],[horizontal],[rotate], | |
| IF( | |
| rotate, | |
| FLIP(TRANSPOSE(array), vertical, horizontal), | |
| IF( | |
| AND(ISOMITTED(vertical), ISOMITTED(horizontal)), | |
| FLIP(array, ROWS(array) > 1, COLUMNS(array) > 1), | |
| CHOOSE( | |
| 1 + AND(vertical) + AND(horizontal) * 2, | |
| array, | |
| SORTBY(array, SEQUENCE(ROWS(array)), -1), | |
| SORTBY(array, SEQUENCE(, COLUMNS(array)), -1), | |
| LAMBDA(i,j, INDEX(array, SEQUENCE(i,, i, -1), SEQUENCE(, j, j, -1)))(ROWS(array), COLUMNS(array)) | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: ROTATE | |
| DESCRIPTION: Rotates a range or array to the left or right by 90 degrees per rotation. | |
| PREREQUISITES: FLIP | |
| SYNTAX: ROTATE(array,[rotations]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [rotations] (optional) - the number of 90 degree rotations to the left (negative) or right (default is 1). | |
| EXAMPLES: =ROTATE(SEQUENCE(5,10)) | |
| =ROTATE(SEQUENCE(5,10),1) | |
| =ROTATE(SEQUENCE(5,10),2) | |
| =ROTATE(SEQUENCE(5,10),3) | |
| =ROTATE(SEQUENCE(5,10),-1) | |
| =ROTATE(SEQUENCE(5,10),-2) | |
| =ROTATE(SEQUENCE(5,10),-3) | |
| */ | |
| ROTATE = LAMBDA(array,[rotations], | |
| CHOOSE( | |
| MOD(ISOMITTED(rotations) + rotations, 4) + 1, | |
| array, | |
| FLIP(array,, 1, 1), | |
| FLIP(array, 1, 1), | |
| FLIP(array, 1,, 1) | |
| ) | |
| ); | |
| /* FUNCTION NAME: FLATTENBLOCKS | |
| DESCRIPTION: Reshapes a range or array by transforming blocks of arbitrary size into rows or columns. | |
| SYNTAX: =FLATTENBLOCKS(array,[block_height],[block_width],[to_cols],[order_mode],[pad_with]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [block_height] (optional) - sets the block height (rows). | |
| [block_width] (optional) - sets the block width (columns). | |
| [to_cols] (Boolean, optional) - specifies the output direction of each block. | |
| 0 or FALSE - blocks to rows (default) | |
| 1 or TRUE - blocks to columns | |
| [order_mode] (integer, optional) - specifies the order in which the array is scanned and the order in which each block is scanned. | |
| 0 - array is scanned in row-major order by block; each block is scanned by row (default) | |
| 1 - array is scanned in col-major order by block; each block is scanned by row | |
| 2 - array is scanned in row-major order by block; each block is scanned by column | |
| 3 - array is scanned in col-major order by block; each block is scanned by column | |
| [pad_with] (scalar, optional) - the value with which to pad the array when its dimensions are not evenly divisible by the block size. | |
| SAMPLE DATA: 1 2 A B | |
| 3 4 C D | |
| 5 6 E F | |
| 7 8 G H | |
| EXAMPLES: =FLATTENBLOCKS(data,2,2) | |
| 1 2 3 4 | |
| A B C D | |
| 5 6 7 8 | |
| E F G H | |
| =FLATTENBLOCKS(data,2,2,TRUE) | |
| 1 A 5 E | |
| 2 B 6 F | |
| 3 C 7 G | |
| 4 D 8 H | |
| =FLATTENBLOCKS(data,2,2,,1) | |
| 1 2 3 4 | |
| 5 6 7 8 | |
| A B C D | |
| E F G H | |
| =FLATTENBLOCKS(data,2,2,1,1) | |
| 1 5 A E | |
| 2 6 B F | |
| 3 7 C G | |
| 4 8 D H | |
| */ | |
| FLATTENBLOCKS = LAMBDA(array,[block_height],[block_width],[to_cols],[order_mode],[pad_with], | |
| LET( | |
| sort_by_col, CHOOSE(order_mode + 1, 0, 1, 0, 1), | |
| scan_by_col, CHOOSE(order_mode + 1, 0, 0, 1, 1), | |
| pad_with, IF(ISOMITTED(pad_with), NA(), pad_with), | |
| h, IF(ISOMITTED(block_height), ROWS(array), block_height), | |
| w, IF(ISOMITTED(block_width), COLUMNS(array), block_width), | |
| r, CEILING.MATH(ROWS(array), h), | |
| c, CEILING.MATH(COLUMNS(array), w), | |
| i, ROUNDUP(SEQUENCE(r) / h, 0), | |
| j, ROUNDUP(SEQUENCE(, c) / w, 0), | |
| a, TOCOL(EXPAND(array, r, c, pad_with),, scan_by_col), | |
| y, TOCOL(IF(j, i),, scan_by_col), | |
| x, TOCOL(IF(i, j),, scan_by_col), | |
| v, IF(sort_by_col, SORTBY(a, x, 1, y, 1), SORTBY(a, y, 1, x, 1)), | |
| IF(to_cols, WRAPCOLS(v, h * w), WRAPROWS(v, h * w)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: HWRAP | |
| DESCRIPTION: Wraps a table or array horizontally a specified number of times and/or after a specified number of rows. | |
| SYNTAX: =HWRAP(array,wrap_count,[row_count],[headers],[pad_with]) | |
| ARGUMENTS: array (required) - the table or array to be transformed. | |
| wrap_count (semi-required) - the number of times the array is wrapped horizontally (if omitted, [row_count] is observed). | |
| [row_count] (optional) - the number of rows included in each wrap (block height). | |
| [headers] (integer, optional) - specifies whether the array has a header row and whether headers should be returned in the results. | |
| 0 - No (default) | |
| 1 - Yes but don't show | |
| 2 - No but generate | |
| 3 - Yes and show | |
| [pad_with] (scalar, optional) - the value with which to pad the array when its dimensions are not evenly divisible by the wrap size. | |
| SAMPLE DATA: X Y | |
| 1 a | |
| 2 b | |
| 3 c | |
| 4 d | |
| 5 e | |
| 6 f | |
| 7 g | |
| 8 h | |
| EXAMPLES: =HWRAP(data,2,,3) | |
| X Y X Y | |
| 1 a 5 e | |
| 2 b 6 f | |
| 3 c 7 g | |
| 4 d 8 h | |
| =HWRAP(data,,2,3) | |
| X Y X Y X Y X Y | |
| 1 a 3 c 5 e 7 g | |
| 2 b 4 d 6 f 8 h | |
| =HWRAP(data,2,2,3) | |
| X Y X Y | |
| 1 a 3 c | |
| 2 b 4 d | |
| 5 e 7 g | |
| 6 f 8 h | |
| */ | |
| HWRAP = LAMBDA(array,wrap_count,[row_count],[headers],[pad_with], | |
| LET( | |
| inc, CHOOSE(headers + 1, 0, 1, 0, 1), | |
| shw, CHOOSE(headers + 1, 0, 0, 1, 1), | |
| p, IF(ISOMITTED(pad_with), NA(), pad_with), | |
| a, DROP(array, inc), | |
| h, ROWS(a), | |
| w, COLUMNS(a), | |
| x, INT(wrap_count), | |
| y, INT(row_count), | |
| v, CHOOSE( | |
| 1 + ISOMITTED(wrap_count) + ISOMITTED(row_count) * 2, | |
| LET( | |
| n, x * y, | |
| m, CEILING.MATH(h, n), | |
| i, SEQUENCE(m / x,, 0), | |
| j, SEQUENCE(, w * x, 0), | |
| INDEX(EXPAND(a, m,, p), QUOTIENT(i, y) * n + MOD(i, y) + 1 + QUOTIENT(j, w) * y, MOD(j, w) + 1) | |
| ), | |
| LET( | |
| j, SEQUENCE(, w * ROUNDUP(h / y, 0), 0), | |
| INDEX(EXPAND(a, CEILING.MATH(h, y),, p), SEQUENCE(y) + QUOTIENT(j, w) * y, MOD(j, w) + 1) | |
| ), | |
| LET( | |
| m, ROUNDUP(h / x, 0), | |
| j, SEQUENCE(, w * x, 0), | |
| INDEX(EXPAND(a, CEILING.MATH(h, x),, p), SEQUENCE(m) + QUOTIENT(j, w) * m, MOD(j, w) + 1) | |
| ), | |
| a | |
| ), | |
| IF( | |
| shw, | |
| LET( | |
| j, MOD(SEQUENCE(, COLUMNS(v), 0), w) + 1, | |
| VSTACK(IF(inc, INDEX(array, 1, j), "Column" & j), v) | |
| ), | |
| v | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: VWRAP | |
| DESCRIPTION: Wraps a range or array vertically a specified number of times and/or after a specified number of columns. | |
| SYNTAX: =VWRAP(array,wrap_count,[col_count],[labels],[pad_with]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| wrap_count (semi-required) - the number of times the array is wrapped vertically (if omitted, [col_count] is observed). | |
| [col_count] (optional) - the number of columns included in each wrap (block width). | |
| [labels] (integer, optional) - specifies whether the array has an identifier field and whether labels should be returned in the results. | |
| 0 - No (default) | |
| 1 - Yes but don't show | |
| 2 - No but generate | |
| 3 - Yes and show | |
| [pad_with] (scalar, optional) - the value with which to pad the array when its dimensions are not evenly divisible by the wrap size. | |
| SAMPLE DATA: X 1 2 3 4 5 6 7 8 | |
| Y a b c d e f g h | |
| EXAMPLES: =VWRAP(data,2,,3) | |
| X 1 2 3 4 | |
| Y a b c d | |
| X 5 6 7 8 | |
| Y e f g h | |
| =VWRAP(data,,2,3) | |
| X 1 2 | |
| Y a b | |
| X 3 4 | |
| Y c d | |
| X 5 6 | |
| Y e f | |
| X 7 8 | |
| Y g h | |
| =VWRAP(data,2,2,3) | |
| X 1 2 5 6 | |
| Y a b e f | |
| X 3 4 7 8 | |
| Y c d g h | |
| */ | |
| VWRAP = LAMBDA(array,wrap_count,[col_count],[labels],[pad_with], | |
| LET( | |
| inc, CHOOSE(labels + 1, 0, 1, 0, 1), | |
| shw, CHOOSE(labels + 1, 0, 0, 1, 1), | |
| p, IF(ISOMITTED(pad_with), NA(), pad_with), | |
| a, DROP(array,, inc), | |
| h, ROWS(a), | |
| w, COLUMNS(a), | |
| y, INT(wrap_count), | |
| x, INT(col_count), | |
| v, CHOOSE( | |
| 1 + ISOMITTED(wrap_count) + ISOMITTED(col_count) * 2, | |
| LET( | |
| m, y * x, | |
| n, CEILING.MATH(w, m), | |
| i, SEQUENCE(h * y,, 0), | |
| j, SEQUENCE(, n / y, 0), | |
| INDEX(EXPAND(a,, n, p), MOD(i, h) + 1, QUOTIENT(j, x) * m + MOD(j, x) + 1 + QUOTIENT(i, h) * x) | |
| ), | |
| LET( | |
| i, SEQUENCE(h * ROUNDUP(w / x, 0),, 0), | |
| INDEX(EXPAND(a,, CEILING.MATH(w, x), p), MOD(i, h) + 1, SEQUENCE(, x) + QUOTIENT(i, h) * x) | |
| ), | |
| LET( | |
| n, ROUNDUP(w / y, 0), | |
| i, SEQUENCE(h * y,, 0), | |
| INDEX(EXPAND(a,, CEILING.MATH(w, y), p), MOD(i, h) + 1, SEQUENCE(, n) + QUOTIENT(i, h) * n) | |
| ), | |
| a | |
| ), | |
| IF( | |
| shw, | |
| LET( | |
| i, MOD(SEQUENCE(ROWS(v),, 0), h) + 1, | |
| HSTACK(IF(inc, INDEX(array, i, 1), "Row" & i), v) | |
| ), | |
| v | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: UNPIVOT | |
| DESCRIPTION: Rotates a table by transforming columns into rows, automatically removing blanks and errors. | |
| SYNTAX: =UNPIVOT(row_labels,col_labels,values,[scan_by_col],[headers]) | |
| ARGUMENTS: row_labels (semi-required) - the row identifier fields, excluding headers (if omitted, RowID is generated). | |
| col_labels (semi-required) - the header rows of the value fields (if omitted, ColumnID is generated). | |
| values (required) - the value fields to be transformed, excluding headers. | |
| [scan_by_col] (Boolean, optional) - specifies the output order. | |
| 0 or FALSE - row-major order (default) | |
| 1 or TRUE - column-major order | |
| [headers] (variant, optional) - specifies whether headers should be returned in the results. | |
| 0 or FALSE - None (default) | |
| 1 or TRUE - Auto-generate | |
| An array of custom column labels | |
| EXAMPLES: =UNPIVOT(Table1[Dept.],Table1[[#Headers],[Jan]:[Mar]],Table1[[Jan]:[Mar]]) | |
| =UNPIVOT(Table1[Dept.],Table1[[#Headers],[Jan]:[Mar]],Table1[[Jan]:[Mar]],TRUE) | |
| =UNPIVOT(A3:B10,C1:E2,C3:E10,1,1) | |
| =UNPIVOT(A3:B10,C1:E2,C3:E10,1,HSTACK(A2:B2,"Year","Month","Sales")) | |
| */ | |
| UNPIVOT = LAMBDA(row_labels,col_labels,values,[scan_by_col],[headers], | |
| LET( | |
| i, IF(ISOMITTED(row_labels), SEQUENCE(ROWS(values)), row_labels), | |
| j, IF(ISOMITTED(col_labels), SEQUENCE(, COLUMNS(values)), col_labels), | |
| w, COLUMNS(i), | |
| h, ROWS(j), | |
| t, values <> "", | |
| λ, LAMBDA(x, TOCOL(IFS(t, x), 2, scan_by_col)), | |
| a, HSTACK( | |
| IF(w = 1, λ(i), CHOOSEROWS(i, λ(SEQUENCE(ROWS(i))))), | |
| IF(h = 1, λ(j), CHOOSEROWS(TRANSPOSE(j), λ(SEQUENCE(, COLUMNS(j))))), | |
| λ(values) | |
| ), | |
| IF( | |
| TYPE(headers) = 64, | |
| VSTACK(TOROW(headers), a), | |
| IF( | |
| headers, | |
| VSTACK( | |
| HSTACK( | |
| IF(ISOMITTED(row_labels), "RowID", "Field" & SEQUENCE(, w)), | |
| IF(ISOMITTED(col_labels), "ColumnID", "Attribute" & SEQUENCE(, h)), | |
| "Value" | |
| ), | |
| a | |
| ), | |
| a | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: UNPIVOT2 | |
| DESCRIPTION: Rotates a table by transforming columns into rows, removing blanks and/or errors as specified. | |
| SYNTAX: =UNPIVOT2(row_labels,col_labels,values,[ignore],[scan_by_col],[headers]) | |
| ARGUMENTS: row_labels (semi-required) - the row identifier fields, excluding headers (if omitted, RowID is generated). | |
| col_labels (semi-required) - the header rows of the value fields (if omitted, ColumnID is generated). | |
| values (required) - the value fields to be transformed, excluding headers. | |
| [ignore] (integer, optional) - specifies the type of values to be removed. | |
| 0 - Keep all values (default) | |
| 1 - Ignore blanks/empty | |
| 2 - Ignore errors | |
| 3 - Ignore blanks/empty and errors | |
| [scan_by_col] (Boolean, optional) - specifies the output order. | |
| 0 or FALSE - row-major order (default) | |
| 1 or TRUE - column-major order | |
| [headers] (variant, optional) - specifies whether headers should be returned in the results. | |
| 0 or FALSE - None (default) | |
| 1 or TRUE - Auto-generate | |
| An array of custom column labels | |
| EXAMPLES: =UNPIVOT2(Table1[Dept.],Table1[[#Headers],[Jan]:[Mar]],Table1[[Jan]:[Mar]]) | |
| =UNPIVOT2(Table1[Dept.],Table1[[#Headers],[Jan]:[Mar]],Table1[[Jan]:[Mar]],3) | |
| =UNPIVOT2(A3:B10,C1:E2,C3:E10,2,TRUE,TRUE) | |
| =UNPIVOT2(A3:B10,C1:E2,C3:E10,1,1,HSTACK(A2:B2,"Year","Month","Sales")) | |
| */ | |
| UNPIVOT2 = LAMBDA(row_labels,col_labels,values,[ignore],[scan_by_col],[headers], | |
| LET( | |
| i, IF(ISOMITTED(row_labels), SEQUENCE(ROWS(values)), row_labels), | |
| j, IF(ISOMITTED(col_labels), SEQUENCE(, COLUMNS(values)), col_labels), | |
| w, COLUMNS(i), | |
| h, ROWS(j), | |
| v, TOCOL(values,, scan_by_col), | |
| λ, LAMBDA(x, TOCOL(IF({1}, x, values),, scan_by_col)), | |
| a, HSTACK( | |
| IF(w = 1, λ(i), CHOOSEROWS(i, λ(SEQUENCE(ROWS(i))))), | |
| IF(h = 1, λ(j), CHOOSEROWS(TRANSPOSE(j), λ(SEQUENCE(, COLUMNS(j))))), | |
| v | |
| ), | |
| b, CHOOSE(ignore + 1, a, FILTER(a, IFERROR(v <> "", 1)), FILTER(a, NOT(ISERROR(v))), FILTER(a, IFERROR(v <> "", 0))), | |
| IF( | |
| TYPE(headers) = 64, | |
| VSTACK(TOROW(headers), b), | |
| IF( | |
| headers, | |
| VSTACK( | |
| HSTACK( | |
| IF(ISOMITTED(row_labels), "RowID", "Field" & SEQUENCE(, w)), | |
| IF(ISOMITTED(col_labels), "ColumnID", "Attribute" & SEQUENCE(, h)), | |
| "Value" | |
| ), | |
| b | |
| ), | |
| b | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SORTBYROW | |
| DESCRIPTION: Sorts the contents of each row in a range or array independently from one another. | |
| SYNTAX: =SORTBYROW(array,[sort_order]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [sort_order] (integer, optional) - specifies the sort order. | |
| 1 - ascending order (default) | |
| -1 - descending order | |
| EXAMPLES: =SORTBYROW(A2:E11) | |
| =SORTBYROW(A2:E11,-1) | |
| */ | |
| SORTBYROW = LAMBDA(array,[sort_order], | |
| LET( | |
| a, TOCOL(array), | |
| i, TOCOL(IFNA(SEQUENCE(ROWS(array)), array)), | |
| o, ISOMITTED(sort_order) + sort_order, | |
| v, SORTBY(a, i, 1, a, o), | |
| WRAPROWS(v, COLUMNS(array)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SORTBYCOL | |
| DESCRIPTION: Sorts the contents of each column in a range or array independently from one another. | |
| SYNTAX: =SORTBYCOL(array,[sort_order]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [sort_order] (integer, optional) - specifies the sort order. | |
| 1 - ascending order (default) | |
| -1 - descending order | |
| EXAMPLES: =SORTBYCOL(A2:E11) | |
| =SORTBYCOL(A2:E11,-1) | |
| */ | |
| SORTBYCOL = LAMBDA(array,[sort_order], | |
| LET( | |
| a, TOCOL(array,, 1), | |
| j, TOCOL(IFNA(SEQUENCE(, COLUMNS(array)), array),, 1), | |
| o, ISOMITTED(sort_order) + sort_order, | |
| v, SORTBY(a, j, 1, a, o), | |
| WRAPCOLS(v, ROWS(array)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SORTBYRC | |
| DESCRIPTION: Sorts the contents of each row or column in a range or array independently from one another. | |
| SYNTAX: =SORTBYRC(array,[sort_order],[sort_by_col]) | |
| ARGUMENTS: array (required) - the range or array to be transformed. | |
| [sort_order] (integer, optional) - specifies the sort order. | |
| 1 - ascending order (default) | |
| -1 - descending order | |
| [sort_by_col] (Boolean, optional) - specifies the sort direction. | |
| 0 or FALSE - sort by row (default) | |
| 1 or TRUE - sort by column | |
| EXAMPLES: =SORTBYRC(A2:E11) | |
| =SORTBYRC(A2:E11,-1) | |
| =SORTBYRC(A2:E11,,TRUE) | |
| =SORTBYRC(A2:E11,-1,1) | |
| */ | |
| SORTBYRC = LAMBDA(array,[sort_order],[sort_by_col], | |
| LET( | |
| a, TOCOL(array,, sort_by_col), | |
| o, ISOMITTED(sort_order) + sort_order, | |
| IF( | |
| sort_by_col, | |
| WRAPCOLS(SORTBY(a, TOCOL(IFNA(SEQUENCE(, COLUMNS(array)), array),, 1), 1, a, o), ROWS(array)), | |
| WRAPROWS(SORTBY(a, TOCOL(IFNA(SEQUENCE(ROWS(array)), array)), 1, a, o), COLUMNS(array)) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RANDOMIZE | |
| DESCRIPTION: Randomizes the values, rows or columns in a range or array. | |
| SYNTAX: =RANDOMIZE(array,[opt]) | |
| ARGUMENTS: array (required) - the range or array to be randomized. | |
| [opt] (integer, optional) - specifies the randomization method. | |
| 0 - All values (default) | |
| 1 - Values by row | |
| 2 - Values by column | |
| 3 - Entire rows | |
| 4 - Entire columns | |
| EXAMPLES: =RANDOMIZE(A2:E11) | |
| =RANDOMIZE(A2:E11,1) | |
| =RANDOMIZE(A2:E11,2) | |
| =RANDOMIZE(SEQUENCE(10,5),3) | |
| =RANDOMIZE(SEQUENCE(10,5),4) | |
| */ | |
| RANDOMIZE = LAMBDA(array,[opt], | |
| LET( | |
| i, ROWS(array), | |
| j, COLUMNS(array), | |
| CHOOSE( | |
| opt + 1, | |
| WRAPROWS(SORTBY(TOCOL(array), RANDARRAY(i * j)), j), | |
| WRAPROWS(SORTBY(TOCOL(array), TOCOL(IFNA(SEQUENCE(i), array)), 1, RANDARRAY(i * j), 1), j), | |
| WRAPCOLS(SORTBY(TOCOL(array,, 1), TOCOL(IFNA(SEQUENCE(, j), array),, 1), 1, RANDARRAY(i * j), 1), i), | |
| SORTBY(array, RANDARRAY(i)), | |
| SORTBY(array, RANDARRAY(, j)) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: TEXTTOCOLS | |
| DESCRIPTION: Splits a single column of delimited text into multiple columns. | |
| SYNTAX: =TEXTTOCOLS(array,delimiter) | |
| ARGUMENTS: array (required) - the text field (column) to be transformed. | |
| delimiter (required) - the character(s) that separate the data. | |
| EXAMPLES: =TEXTTOCOLS(A2:A10,"|") | |
| =TEXTTOCOLS(A2:A10,", ") | |
| */ | |
| TEXTTOCOLS = LAMBDA(array,delimiter, | |
| LET( | |
| n, (LEN(array) - LEN(SUBSTITUTE(array, delimiter, ))) / LEN(delimiter), | |
| m, MAX(n) + 1, | |
| TEXTBEFORE( | |
| TEXTAFTER( | |
| delimiter & array & REPT(delimiter, m - n), | |
| delimiter, | |
| SEQUENCE(, m) | |
| ), | |
| delimiter | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: TEXTTOCOLS2 | |
| DESCRIPTION: Splits a single column of delimited text into multiple columns (for use with TOCOL/TOROW to ignore errors). | |
| SYNTAX: =TEXTTOCOLS2(array,delimiter) | |
| ARGUMENTS: array (required) - the text field (column) to be transformed. | |
| delimiter (required) - the character(s) that separate the data. | |
| EXAMPLES: =TEXTTOCOLS2(A2:A10,"|") | |
| =UNIQUE(SORT(TOCOL(TEXTTOCOLS2(A2:A10,", "),2))) | |
| =LET(a,TEXTTOCOLS2(A2:A10,", "),HSTACK(TOCOL(IF(ISERROR(a),a,SEQUENCE(ROWS(a))),2),TOCOL(a,2))) | |
| */ | |
| TEXTTOCOLS2 = LAMBDA(array,delimiter, | |
| TEXTSPLIT( | |
| TEXTAFTER( | |
| delimiter & array, | |
| delimiter, | |
| SEQUENCE(, MAX(LEN(array) - LEN(SUBSTITUTE(array, delimiter, ))) / LEN(delimiter) + 1) | |
| ), | |
| delimiter | |
| ) | |
| ); | |
| /* FUNCTION NAME: Nz | |
| DESCRIPTION: Returns a zero-length string or another specified value when a variant is null (derived from a blank cell in a range). | |
| SYNTAX: =Nz(range,[value_if_blank]) | |
| ARGUMENTS: range (required) - the range to be evaluated. | |
| [value_if_blank] (optional) - the value to be returned if a cell within the range is blank (default is ""). | |
| EXAMPLES: =SORTBYCOL(Nz(A2:E11)) | |
| =Nz(SORTBYCOL(A2:E11),"x") | |
| =UNPIVOT2(Table1[Dept.],Table1[[#Headers],[Jan]:[Apr]],Nz(Table1[[Jan]:[Apr]]),,TRUE) | |
| =Nz(UNPIVOT2(Table1[Dept.],Table1[[#Headers],[Jan]:[Apr]],Table1[[Jan]:[Apr]],,TRUE)) | |
| */ | |
| Nz = LAMBDA(range,[value_if_blank], | |
| IF( | |
| ISBLANK(range), | |
| IF(ISOMITTED(value_if_blank), "", value_if_blank), | |
| range | |
| ) | |
| ); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment