Spread.NET 13 for WinForms includes support for dynamic arrays and six important new functions: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY, and UNIQUE. This blog describes each of those new functions in-depth.
Read the full Spread.NET release
To illustrate these functions, I have created a worksheet for each new function that describes the function and its' arguments. I'll also show several examples to illustrate it clearly.
These worksheets are the first six worksheets in this workbook:
Download the Spilled_Array_Function_Examples
You can use the Spread Designer tool that is included with Spread.NET 13 Windows Forms. Download a 30-day free trial here to open the XLSX and follow along with the examples.
FILTER
Filter Syntax
| FILTER (array, include, [if_empty]) | |
| array | The array or reference to filter. |
| include | The filter condition – see examples below |
| [if_empty] | Optional value to return when the filter result is empty. |
The FILTER function takes a range or array and returns a filtered array using the specified include criteria. The first argument array specifies the range or array to filter, the second argument include specifies the include criteria, and the optional third argument if_empty specifies the value to return when the filter result is empty.
When the if_empty argument is not specified and the filter result is empty, the function returns a #CALC! error value.
To illustrate the FILTER function in action, here is a sample table named FilterData:
Figure 1 - FilterData table of sample data for FILTER function
To specify the include criteria, you must specify a range or array of the same length as the filter data with the = comparison operator and value to filter by. You can also use <, <=, >, and >= operators if the filter data is date or numeric type.
For example, to filter the above table by Product Line to show only the Spread.NET items, use this formula:
=FILTER(FilterData,FilterData[Product Line]="Spread.NET")
Figure 2 FILTER function example 1
Note that the above result in B27:F33 is formatted to appear like a table, but is not actually a table, as dynamic array formulas and spilling behavior are not supported in table cells. Instead, the range is formatted to appear like a table and the table header cells B26:F26 are merely copies of the original table header cells.
The Total in row 34 is also added manually, with a formula in cell F34 to sum the cells above (you can insert such a formula using the Alt+= keyboard shortcut using Spread.NET 13 WinForms).
The result in cell B27 can be filtered again using the spilled range reference operator # to reference the formula cell and perform another FILTER. This example shows using the explicit intersection to specify the cell range for the criteria by using the column reference C:C and the range intersection operator (the space character) with the formula cell reference and the spilled range operator:
=FILTER(B27#,B27# C:C="NewLicense")
Figure 3 FILTER function example 2
The expression B27# C:C in the formula for cell H27 above uses the range intersection operator (the space character) to perform an explicit intersection between the cell range B27#, which is the spilled range B27:F33 for the formula in B27. The column range C:C, which is all cells in column C, which results in the range C27:C33, the cells in the Product table column.
If the filter criteria instead specified “Deployment License” for the product, and nothing is specified for the is_empty argument, then the filter result would be a #CALC! error and look like this:
=FILTER(B27#,B27# C:C="DeploymentLicense")
Figure 4 FILTER function example 3
When the is_empty argument is specified, that value is returned instead of the #CALC! error:
=FILTER(B27#,B27# C:C="DeploymentLicense","NONE FOUND")
Figure 5 FILTER function example 4
More complex filter conditions can be specified by using the * and + operators to combine multiple filter criteria enclosed in parentheses.
The * operator can specify “AND” between two filter criterial, and the + operator can specify “OR” allowing for two or more criterial to be combined in one FILTER function call.
This example filters by two criteria and uses the * operator to specify AND – that both conditions must be satisfied:
=FILTER(FilterData,(FilterData[Product Line]="SpreadJS")*(FilterData[Product]="DeploymentLicense"))
Figure 6 FILTER function example 5
RANDARRAY
RANDARRAY Syntax
| RANDARRAY([rows],[columns],[min],[max],[integer]) | |
| [rows] | Number of rows of random numbers to generate (default is 1). |
| [columns] | Number of columns of random numbers to generate (default is 1). |
| [min] | Minimum of values to generate (default is 0). |
| [max] | Maximum of values to generate (default is 1). |
| [integer] | TRUE to return integer values (default is FALSE). |
| RANDARRAY is a volatile function which recalculates each time any change is made in the worksheet. | |
The RANDARRAY function is a powerful new function that makes RAND and RANDBETWEEN obsolete. When used without any arguments, RANDARRAY works just like the RAND function and returns a single random number between 0 and 1, but when specifying the optional arguments you can generate arrays of random numbers that can be integer or decimal values and with any required minimum and maximum values.
The RANDARRAY function is a volatile function which automatically recalculates and generates a new random array of results in each calculation cycle. This function can be used to generate random sample data for Monte Carlo style statistical and stochastic analysis.
This example generates a 10 row by 15 column array of random integer values between 100 and 500:
Figure 7 RANDARRAY function example
Generally, RANDARRAY will specify hard-coded arguments to fill a range with the required random array of values to meet the requirements of the task at hand, and since all arguments of RANDARRAY are optional, this formula shows how the RANDARRAY function would operate if arguments are omitted by supplying the correct default values when the input cell for the argument is empty:
=RANDARRAY(IF(ISBLANK(B13),1,B13),IF(ISBLANK(B14),1,B14), IF(ISBLANK(B15),0,B15),IF(ISBLANK(B16),1,B16), IF(ISBLANK(B17),FALSE,B17))
You can change or remove arguments in the worksheet to try out different combinations and see how RANDARRAY operates to generate new random data for each change.
SEQUENCE
SEQUENCE syntax
| SEQUENCE (rows,[columns],[start],[step]) | |
| rows | Number of rows to generate in the sequence (required – if not provided, result is #CALC! error). |
| [columns] | Number of columns to generate in the sequence (default is 1). |
| [start] | Optional starting value (default is 1). |
| [step] | Optional increment value (default is 1). |
The SEQUENCE function seems so deceptively simple, but is actually a very powerful new function with some surprisingly flexible applications for dynamic arrays. This function generates a sequence of values using the specified optional start and step increment.
This example generates an array, 10 columns by 5 rows, of sequential values starting from 5 and incrementing by 5:
Figure 8 SEQUENCE function example 1
Note that the values are generated from left to right, then top to bottom. As in the example for RANDARRAY, this example uses a complex formula to supply the default values for SEQUENCE when the input cells for the function arguments are empty to simulate using the function in the most common manner with hard-coded arguments:
=SEQUENCE(B10,IF(ISBLANK(B11),1,B11),IF(ISBLANK(B12),1,B12),IF(ISBLANK(B13),1,B13))
You can change or remove arguments in the worksheet to try out different combinations and see how SEQUENCE operates.
The real power of SEQUENCE comes from combining it with other functions. This example generates a six-week calendar layout of dates using SEQUENCE with TODAY:
Figure 9 SEQUENCE function example 2
The formula above could instead use a cell reference for the start value, and then start from a specified date in another cell or calculate the date in some other way.
This example creates a schedule of time slots every ten minutes starting from the current time using SEQUENCE and NOW:
Figure 10 SEQUENCE function example 3
Once again, the formula above could use a cell reference or other calculation for the start value instead of using the NOW function.
Sometimes SEQUENCE is useful for generating arguments for other functions to turn those functions into dynamic array functions.
This doesn’t work with all functions, but for many functions, it does work to pass an array of arguments using the SEQUENCE function and the result will calculate for each value and spill.
This example uses the LARGE function with SEQUENCE to return an array of the N largest values in a range:
Figure 11 SEQUENCE function example 4
The LARGE function expects a scalar value for the second argument, so passing SEQUENCE(G3) in this case specifies the array value “{1;2;3}” which causes the calculation to get “lifted” and the LARGE function is performed three times, once for each element in the array, and then returns an array result containing the three largest values in the random data range.
This example is very similar and uses the SMALL function to return an array of the N smallest values in a range:
Figure 12 SEQUENCE function example 5
The above example also shows two different ways of generating horizontal instead of vertical values that spill to adjacent cells. The first way uses the TRANSPOSE function, which transposes the columns and rows of the specified array value. The second example demonstrates using the SEQUENCE function to generate the sequence of values horizontally instead of vertically, which eliminates the need to use the TRANSPOSE function and returns the same results more efficiently.
This last example for SEQUENCE uses TEXTJOIN with SEQUENCE, CHAR, and CODE to generate sequences of letters separated by dashes:
Figure 13 SEQUENCE function example 6
The formulas in the cells C6:C10 are copied down the column, but the formula in cell D6 uses the FORMULATEXT function with the range argument C6:C10, so the result spills down to the adjacent cells.
SINGLE
SINGLE Syntax
| SINGLE(value) | When using the SINGLE function in a formula, it is automatically replaced with this syntax using the new ‘@’ operator |
| @value | |
| value | Reference to a range to intersect with the formula cell, or array (returns the first element). |
The SINGLE function and the new @ operator are very important for backwards compatibility of formulas. Older versions of Excel that do not support dynamic arrays automatically perform implicit intersection when a range is specified for a function argument that expects a scalar value – so the old behavior of Excel would intersect the specified range with the formula cell and use the appropriate value from the specified range that intersects with the formula cell row or column, depending on the specified range and its dimensions.
In most cases, the cell range specified must be a single-dimensional array of either vertical or horizontal values because the intersection must be unique – a range of cells in one row or a range of cells in one column.
With the introduction of dynamic arrays and the new spilling behavior, you must specify the SINGLE function or use the new @ operator to explicitly specify that the old range intersection behavior is required, instead of “lifting” the array argument and calculating the formula for each element in the array, then spilling the resulting array to adjacent cells.
Formulas that are imported and contain range references for function arguments, or formulas set in code using the IRange.Formula property, are automatically modified to insert @ before the range arguments to preserve backwards compatibility with calculations.
To set formulas that are intended to use the new dynamic array features and spill results, you must specify those formulas in code using the new IRange.Formula2 property:
[C#]
IWorkbook wb = fpSpread1.AsWorkbook();
// note: to set a Dynamic Array formula in code that is intended to "spill" you MUST use the new Formula2 property!
wb.Worksheets["SINGLE"].Range("C8").Formula2 = "{1,2,3,4,5}";
[VB]
Dim wb As IWorkbook = FpSpread1.AsWorkbook()
' note: to set a Dynamic Array formula in code that is intended to "spill" you MUST use the new Formula2 property!
wb.Worksheets("SINGLE").Range("C8").Formula2 = "{1,2,3,4,5}"
Figure 14 Set dynamic array formula using new Formula2 property
If you set a formula using an array value or range argument that would spill using the IRange.Formula property, then that formula will automatically be adjusted to insert @ before each array and range reference.
This is the same logic applied to formulas imported from older spreadsheet documents saved with legacy formulas that were created when using such array values or range arguments would automatically cause those formulas to either select the first value in the array or implicitly intersect the range with the formula cell – now such array values or range arguments must use the SINGLE function or @ operator to calculate as before.
Setting the array value above using the Formula property instead of Formula2 results in the formula =@{1,2,3,4,5}, which only shows the first value in cell C8 and doesn’t spill to adjacent cells:
Figure 15 Set formula using Formula property will insert '@' for backwards compatibility
Note that the higher-level wrapper objects like Cell, Column, and Row do not have the Formula2 property. When using those objects, the formula will not be adjusted to insert @ before hard-coded array and range references, and instead those formulas will spill to adjacent cells when dynamic arrays are enabled. You can control whether the calculation engine enables dynamic array formulas and spilling behavior using the CalcFeatures flags in the WorkbookSet:
[C#]
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray;
[VB]
FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.DynamicArray
When you use the new SINGLE function in a cell formula, it is automatically replaced with the equivalent syntax using the new @ operator instead, to simplify the formula expression. The examples below show using the SINGLE function with horizontal and vertical arrays and with hard-coded array values:
The formula in cell D10 =@C8# references the horizontal array in the cell C8 which spills to G8 – the result is 2 because the intersection between the range C8:G8 and the formula cell D10 returns the value in the cell D8 since that is the column in the range C8:G8 which intersects with the formula cell D10. The formula in cell D11 references the hard-coded array value, which returns the first item in the array, 1 in this case. The formulas in cells D15 and F15 operate similarly with the vertical array in cell B14.
The SINGLE function can also be used to select a single value from a cell range by specifying an intersection range that evaluates to a single cell:
The cell formula in cell C22 returns a three-by-three array of values which spills to adjacent cells in the range C22:E24.
The contents of that range are mirrored in two ways using the SINGLE function, first in the range G22:I24 using the column references C:C, D:D, and E:E to intersect the formula cell with the cell of the specified adjacent column. Then again in the range C27:E29 using the row references 22:22, 23:23, and 24:24 to intersect the formula cell with the cell of the specified adjacent row.
In each case, the formulas must be copied down for the column formulas and across for the row formulas, so nine formulas are required for each since each formula returns a single value from the range.
SORT
SORT syntax
| SORT(array,[sort_index],[sort_order],[by_col]) | |
| array | Array or range to sort. |
| [sort_index] | Optional index of the row or column to sort by (default is 1). |
| [sort_order] | Optional -1 to sort descending (default is 1, ascending). |
| [by_col] | Optional TRUE to sort by column (default is FALSE, sort by row). |
The SORT function takes an array or range of values and performs a sort on the contents using the specified sort indices and sort orders for each respective sort key. The array argument can be a hard-coded array of values, a range reference, or some more complex expression which results in an array of values (for example, using another dynamic array function like UNIQUE or SORTBY).
The sort_index and sort_order arguments can be scalar values in the case of a simple 1-key sort, or they can be array values (which must be the same length) that specify respective sort keys to use for a multiple-key sort. The by_col argument specifies whether the sort should be performed by rows (this is the default behavior) using the sort index to specify the key column indices, or instead by column using the sort index to specify the key row indices. This function is useful for cases like the content of a table, where the key columns or rows are contained inside the table data.
For cases where the table data needs to be sorted based on a key value which is not part of the table data, use the SORTBY function instead (see below).
The examples below use this SortData table:
The first example sorts on the ProductName column in the default ascending order:
The next example sorts on the Quantity in descending order:
The last example sorts on the SalesPerson in ascending order, then Quantity in descending order:
SORTBY
SORTBY syntax
| SORYBY(array,by_array1,[order_array1],[by_array2, order_array2],…) | |
| array | Array or range to be sorted (required). |
| by_array | Array or range of first sort key (required). |
| [order_array1] | Optional -1 for descending order (default is 1, ascending). |
| [by_array2 | Optional array or range of second sort key. |
| order_array2] | -1 for descending, 1 for ascending (required if by_array2 specified). |
| This function can accept additional arguments in pairs, specifying the next sort key range and order. | |
The SORTBY function is useful in cases where you want to sort a range or array by some key column or row that is not part of the array or range. This function is thus more flexible than the SORT function, as the by_array can be any range or array of the appropriate size.
The examples below use this SortByData table (which contains identical data to the SortData table from earlier for clarity):
The first example sorts on the ProductName column in the default ascending order:
The next example sorts on the Quantity in descending order:
The last example sorts on the SalesPerson in ascending order, then Quantity in descending order:
Note that instead of specifying column or row indices in the range for sort keys, when using the SORTBY function you can instead use structured table references to the entire table column, which is much clearer and easier to read than the formulas using the SORT function.
UNIQUE
UNIQUE syntax
| UNIQUE(array,[by_col],[unique_only]) | |
| array | Array or range containing values or vectors to be compared. |
| by_col | Whether to compare values by column or by row (default is FALSE, by row). |
| unique_only | Whether to return only unique values or vectors (occurring exactly once, default is FALSE). |
The UNIQUE function takes an array or range of values and returns an array of unique items. This function is flexible with its definition of “unique” depending on whether you specify TRUE or FALSE for the optional unique_only argument – if you specify TRUE, then the function searches the array for only the unique items that occur exactly once and returns a list of those items only. However, the default behavior is FALSE, which “cleans” the array of duplicates and return a list of each item in the original array with no duplicates.
These first examples of UNIQUE show how you must specify TRUE for by_col when array is a horizontal array that spills to adjacent columns, or else UNIQUE will not return the unique items as expected:
These next examples of UNIQUE show similarly how to work with a vertical array:
The formula in cell E22 shows the most common usage of UNIQUE – specifying a vertical array or reference to a range of cells in one column does not require any other arguments to return the “cleaned” list of unique items. This is equivalent to the formula in cell H22 which specifies the default value FALSE for by_col. The formula in cell K22 shows using TRUE for unique_only and returns only one item in this case, 3, since this is the only unique value in the range.
You should not specify TRUE for by_col when array is a vertical array that spills to adjacent rows:
The UNIQUE function gets more complicated when used with a two-dimensional array of values or a cell range – the following examples reference this hard-coded array ={1,1,2,1,3;1,1,2,1,3;1,2,3,2,5;1,2,3,2,5;1,2,2,2,5} spilling to adjacent cells and filling the range D29:H33:
When the array is two-dimensional, then it's treated as a list of row vectors when by_col is FALSE or not specified, and as a list of column vectors when by_col is TRUE. Comparisons are performed between vector values, and two vector values are considered the same when each vector component (corresponding cell value) is the same.
This is somewhat complicated, so in order to illustrate this clearly, I will split this example into 9 cases referencing the above array in all the possible ways and explain each outcome in detail.
Case 1 simply references the range D29# and leaves out the optional arguments:
This returns three unique rows since the default values for by_col and unique_only are both FALSE. The next two cases, 2 and 3, are related and show the same result by specifying the default value(s) for those arguments:
Case 4 shows the result of specifying FALSE for by_col and TRUE for unique_only, returning one unique row:
Cases 5 and 6 show the result of explicitly specifying TRUE for _by_col_ and FALSE for unique_only, using the default value in case 5 and explicitly specifying FALSE in case 6, thus returning four unique columns:
Case 7 shows the same result as cases 1, 2, and 3, leaving by_col using the default value FALSE and explicitly specifying FALSE for unique_only:
Case 8 shows the result of specifying TRUE for by_col and unique_only, which results in three unique columns:
Finally, Case 9 shows the same result as case 4, leaving by_col using the default value FALSE and explicitly specifying TRUE for unique_only:
Further Reading:
- Create a Crosstab Report with Three Simple Dynamic Array Formulas
- Drop-down Lists and Dynamic Array Formulas in Spread.NET 13 Windows Forms
Sean Lawyer