Dynamic Array Formulas

SpreadJS provides extensive support for using dynamic array formulas in spreadsheets.

Usage Scenario

Dynamic Array Formulas return multiple results as output via automatic spilling and spanning to the cell range. This makes it much easier and quicker to work with array formulas in spreadsheets.

Dynamic array formulas are useful, especially when:

Users want to implement effective utilization of data caches in their spreadsheets.
Users want to build cache-friendly data structures that facilitate random access with low memory footprints (compactness). Generally, this is possible because dynamic arrays have only a small fixed additional overhead for storing information about the size and capacity.

UNIQUE Function

The Unique function returns a list of all the unique values in a cell range.

For instance - Cell C4 contains the formula "=UNIQUE(A4:A15)" and returns only the unique customer names from the values in cell range A4 to A15. Based on the number of unique values, the dynamic array formula spills to the cell range C5 to C8 automatically, as shown below:

FILTER Function

The FILTER function allows users to filter a cell range on the basis of the defined criteria. The filter operation can be performed based on a single criterion or multiple criteria. In order to combine two or more filter conditions, users can use the " * " operator.

For instance - The cell F5 contains the formula "=FILTER(A5:D17, C5:C17=F1)". This formula filters the cell range A5 to D17 based on one filter criteria (when the cell range C5 to C17 matches the Product value in cell F1 i.e. Apple). As a result, all the values in the cell range A5 to D17 containing the product "Apple" will be displayed.

The cell F14 in the above image contains the formula "=FILTER(A5:D17, (C5:C17=F1)(A5:A17=F2))". This formula filters the cell range A5 to D17 based on two filter conditions that are specified by the multiplication () operator. The first condition is the cell range C5 to C17 should match the Product value in cell F1 i.e. Apple and the second condition is the cell range A5 to A17 should match the region "East". As a result, all the values in the cell range A5 to D17 containing Product as "Apple" and Region as "East" will be displayed.

RANDARRAY Function

The RANDARRAY function returns an array of random numeric values. Users can specify the number of rows and columns, and minimum and maximum values, and indicate whether to return integers or decimal values.

For instance- Cell A8 in the following image contains the formula "=RANDARRAY(5,3)" and returns a random set of values between 0 and 1.

SORT Function

The SORT function sorts the data in a cell range or an array. The results of this function spill into the resultant range with a dynamic array of values arranged in the ascending (increasing) or descending (decreasing) order. If the sort order is not specified, then by default, the values are sorted alphabetically in the ascending order.

For instance- Cell D4 contains the formula "=SORT(A4:A15)" and returns the customer names sorted in increasing order.

In case you want to sort all the unique values in the range A4 to A15, you can either apply the sort function on the unique list displayed in column C4 or you can also combine both the functions SORT and UNIQUE into a single formula.

For instance, cell E4 contains the formula "=SORT(C4#)" where # indicates a list. This formula will sort the list of values in column C (where cell C4 already contains the UNIQUE formula "=UNIQUE(A4:A15)") and displays the results in column E.

Alternatively, you can also combine both the functions SORT and UNIQUE. For instance, the cell F4 in the following image contains the formula "=SORT(UNIQUE(A4:A15))" which returns all the unique values in the range A4:A15 sorted alphabetically.

SORT BY Function

The SORTBY function sorts the contents of a cell range or an array on the basis of the values present in a corresponding range or array.

For instance - The cell G4 contains the formula "=SORTBY(A4:B15,B4:B15)". This function sorts the cell range A4 to B15 based on another cell range B4 to B15 and returns the customer names displayed along with their ages sorted in the increasing order.

SEQUENCE Function

The SEQUENCE function returns a list of sequential numbers in an array in the ascending order.

For instance - Cell A2 contains the formula "=SEQUENCE(4,5)" and returns an array with values spilled to a cell range containing four rows and five columns displaying numbers in the sequence 1, 2, 3, 4 up to 20.

SINGLE Function

The SINGLE function returns a single value, a single cell range, or an error using the implicit intersection logic.

For instance - Cell A15 contains the formula "=SINGLE(A15:E15)" and returns the result "C" in cell C16 by evaluating the intersection of the rows and columns in the cell range A15 to E15.

Spilled Array Formulas

Working with generic formulas in spreadsheets is a cumbersome task because users need to manually copy the formulas to every cell where they want the result to be calculated. When a cell contains a dynamic array formula, the elements of the array spill into the adjacent empty cells, and multiple values are returned. Unlike generic arrays, dynamic arrays automatically resize when the data is inserted or removed from the source range.

Spilling - When all the formulas return multiple values (in an array), the neighboring cells will be populated with the results (calculated data). This behavior is called spilling. Any formula that possesses the potential to return multiple results can be referred to as a dynamic array formula.

Spilled Array Formulas - Formulas that return multiple results across the cell range in the spreadsheet, and are successfully spilling, are called spilled array formulas.

Spill Range Operator - The spill range operator (#) can be used to reference the entire spill range. Spill errors are returned when a formula returns multiple results.

Enable Dynamic Array Functions

In order to use dynamic array functions in spreadsheets, users first need to enable dynamic array following code snippet.

// Enable Dynamic Array
spread.options.allowDynamicArray = true;

The following code sample demonstrates how the dynamic array functions are used in the spreadsheet.

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 4 });

// Get the activesheet
var activeSheet = spread.getActiveSheet();

// Enable Dynamic Array
spread.options.allowDynamicArray = true;
activeSheet.FrozenRowCount = 1;
activeSheet.setText(0, 0, "Dynamic Array Functions");
activeSheet.getCell(0, 0).backColor("LightGray");
activeSheet.addSpan(0, 0, 1, 3, GC.Spread.Sheets.SheetArea.viewport);
// Setting Data in Cells of Sheet[0]
activeSheet.setText(2, 0, "Customer's Name");
activeSheet.getCell(2, 0).backColor("LightGray");
activeSheet.setText(3, 0, "Larry");
activeSheet.setText(4, 0, "Safeway");
activeSheet.setText(5, 0, "Safeway");
activeSheet.setText(6, 0, "Raley");
activeSheet.setText(7, 0, "Vallarta");
activeSheet.setText(8, 0, "Safeway");
activeSheet.setText(9, 0, "Raley");
activeSheet.setText(10, 0, "Larry");
activeSheet.setText(11, 0, "Gilbert");
activeSheet.setText(12, 0, "Larry");
activeSheet.setText(13, 0, "Larry");
activeSheet.setText(14, 0, "Raley");
activeSheet.setColumnWidth(0, 120.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(2, 1, "Age");
activeSheet.getCell(2, 1).backColor("LightGray");
activeSheet.setText(3, 1, "32");
activeSheet.setText(4, 1, "23");
activeSheet.setText(5, 1, "23");
activeSheet.setText(6, 1, "39");
activeSheet.setText(7, 1, "18");
activeSheet.setText(8, 1, "23");
activeSheet.setText(9, 1, "39");
activeSheet.setText(10, 1, "32");
activeSheet.setText(11, 1, "19");
activeSheet.setText(12, 1, "32");
activeSheet.setText(13, 1, "32");
activeSheet.setText(14, 1, "39");
activeSheet.setColumnWidth(1, 50.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Unique" Formula
activeSheet.setText(2, 2, "Unique List");
activeSheet.getCell(2, 2).backColor("LightBlue");
activeSheet.setFormula(3, 2, "UNIQUE(A4:A15)");
activeSheet.setColumnWidth(2, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Sort" Formula
activeSheet.setText(2, 3, "Sort");
activeSheet.getCell(2, 3).backColor("LightBlue");
activeSheet.setFormula(3, 3, "SORT(A4:A15)");
activeSheet.setColumnWidth(3, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Sort" Formula for Unique list
activeSheet.setText(2, 4, "Sort Unique");
activeSheet.getCell(2, 4).backColor("LightBlue");
activeSheet.setFormula(3, 4, "SORT(C4#)");
activeSheet.setColumnWidth(4, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Sort+Unique" Formula together
activeSheet.setText(2, 5, "Sort Unique");
activeSheet.getCell(2, 5).backColor("LightBlue");
activeSheet.setFormula(3, 5, "SORT(UNIQUE(A4:A15)");
activeSheet.setColumnWidth(5, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "SortBy" Formula wherein we sort Range A4:B15 based on the values in a corresponding range B4:B15
activeSheet.setText(2, 6, "SortBy");
activeSheet.getCell(2, 6).backColor("LightBlue");
activeSheet.setFormula(3, 6, "SORTBY(A4:B15, B4:B15)");
activeSheet.setColumnWidth(6, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// get the second sheet
var activeSheet = spread.getSheet(1);
// Setting Data in Cells of Sheet[1]
for (var i = 0; i < 9; i++)
     activeSheet.setColumnWidth(i, 70.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(3, 0, "Region");
activeSheet.getCell(3, 0).backColor("LightGray");
activeSheet.setText(4, 0, "East");
activeSheet.setText(5, 0, "North");
activeSheet.setText(6, 0, "Wast");
activeSheet.setText(7, 0, "Sast");
activeSheet.setText(8, 0, "East");
activeSheet.setText(9, 0, "East");
activeSheet.setText(10, 0, "West");
activeSheet.setText(11, 0, "South");
activeSheet.setText(12, 0, "North");
activeSheet.setText(13, 0, "North");
activeSheet.setText(14, 0, "East");
activeSheet.setText(15, 0, "South");
activeSheet.setText(16, 0, "West");
activeSheet.setText(3, 1, "Sales Rep");
activeSheet.getCell(3, 1).backColor("LightGray");
activeSheet.setText(4, 1, "Tom");
activeSheet.setText(5, 1, "Fred");
activeSheet.setText(6, 1, "Amy");
activeSheet.setText(7, 1, "Sal");
activeSheet.setText(8, 1, "Hector");
activeSheet.setText(9, 1, "Xi");
activeSheet.setText(10, 1, "Amy");
activeSheet.setText(11, 1, "Sal");
activeSheet.setText(12, 1, "Fred");
activeSheet.setText(13, 1, "Tom");
activeSheet.setText(14, 1, "Hector");
activeSheet.setText(15, 1, "Sravan");
activeSheet.setText(16, 1, "Xi");
activeSheet.setText(3, 2, "Product");
activeSheet.getCell(3, 2).backColor("LightGray");
activeSheet.setText(4, 2, "Apple");
activeSheet.setText(5, 2, "Grape");
activeSheet.setText(6, 2, "Pear");
activeSheet.setText(7, 2, "Banana");
activeSheet.setText(8, 2, "Apple");
activeSheet.setText(9, 2, "Banana");
activeSheet.setText(10, 2, "Banana");
activeSheet.setText(11, 2, "Pear");
activeSheet.setText(12, 2, "Apple");
activeSheet.setText(13, 2, "Grape");
activeSheet.setText(14, 2, "Grape");
activeSheet.setText(15, 2, "Apple");
activeSheet.setText(16, 2, "Grape");
activeSheet.setText(3, 3, "Units");
activeSheet.getCell(3, 3).backColor("LightGray");
activeSheet.setText(4, 3, "6380");
activeSheet.setText(5, 3, "2344");
activeSheet.setText(6, 3, "3434");
activeSheet.setText(7, 3, "5461");
activeSheet.setText(8, 3, "2341");
activeSheet.setText(9, 3, "3234");
activeSheet.setText(10, 3, "6532");
activeSheet.setText(11, 3, "7323");
activeSheet.setText(12, 3, "2334");
activeSheet.setText(13, 3, "8734");
activeSheet.setText(14, 3, "1932");
activeSheet.setText(15, 3, "7682");
activeSheet.setText(16, 3, "3293");
activeSheet.setText(0, 4, "Product:");
activeSheet.getCell(0, 4).backColor("LightGray");
activeSheet.setText(0, 5, "Apple");
activeSheet.setText(1, 4, "Region:");
activeSheet.getCell(1, 4).backColor("LightGray");
activeSheet.setText(1, 5, "East");
activeSheet.setText(2, 5, "Filtering performed on one Criteria");
activeSheet.getCell(2, 5).backColor("LightBlue");
activeSheet.addSpan(2, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(3, 5, "Region");
activeSheet.getCell(3, 5).backColor("LightGray");
activeSheet.setText(3, 6, "Sales Rep");
activeSheet.getCell(3, 6).backColor("LightGray");
activeSheet.setText(3, 7, "Product");
activeSheet.getCell(3, 7).backColor("LightGray");
activeSheet.setText(3, 8, "Units");
activeSheet.getCell(3, 8).backColor("LightGray");
// Setting "Filter" Formula( with one condition) wherein we filter range A5:D21
// based upon criteria wherein range C5:C21 is equal to value in cell F1
activeSheet.setFormula(4, 5, "FILTER(A5:D21, C5:C21=F1)", GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(12, 5, "Region");
activeSheet.getCell(12, 5).backColor("LightGray");
activeSheet.setText(12, 6, "Sales Rep");
activeSheet.getCell(12, 6).backColor("LightGray");
activeSheet.setText(12, 7, "Product");
activeSheet.getCell(12, 7).backColor("LightGray");
activeSheet.setText(12, 8, "Units");
activeSheet.getCell(12, 8).backColor("LightGray");
activeSheet.setText(11, 5, "Filtering performed on two Criteria");
activeSheet.getCell(11, 5).backColor("LightBlue");
activeSheet.addSpan(11, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Filter" Formula( with two conditions) wherein we filter range A5:D21
// based upon criteria wherein range C5:C21 is equal to value in cell F1
// and range A5:A21 is equal to value in cell F2
activeSheet.setFormula(13, 5, "FILTER(A5:D21, (C5:C21=F1)*(A5:A21=F2))", GC.Spread.Sheets.SheetArea.viewport);
// get the second sheet
var activeSheet = spread.getSheet(2);
for (var i = 0; i < 7; i++)
activeSheet.setColumnWidth(i, 130.0, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Sequence" FormulaactiveSheet.Columns[0, 7].Width = 130;
activeSheet.setText(0, 0, "SEQUENCE(4,5) Function");
activeSheet.addSpan(0, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(0, 0).backColor("SkyBlue");
activeSheet.setFormula(1, 0, "SEQUENCE(4,5)", GC.Spread.Sheets.SheetArea.viewport);
// Setting "RandArray" Formula
activeSheet.setText(6, 0, "RANDARRAY(5,3) Function");
activeSheet.addSpan(6, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(6, 0).backColor("SkyBlue");
activeSheet.setFormula(7, 0, "RANDARRAY(5,3)", GC.Spread.Sheets.SheetArea.viewport);
// Setting "Single" Formula
activeSheet.setText(13, 0, "SINGLE Function is represented by @ i.e. =@(A15:E15)");
activeSheet.addSpan(13, 0, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(13, 0).backColor("SkyBlue");
activeSheet.setValue(14, 0, "A");
activeSheet.setValue(14, 1, "B");
activeSheet.setValue(14, 2, "C");
activeSheet.setValue(14, 3, "D");
activeSheet.setValue(14, 4, "E");
activeSheet.setFormula(15, 2, "@(A15:E15)", GC.Spread.Sheets.SheetArea.viewport);