SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage User Interface / Formulas / Dynamic Array Formulas
In This Topic
    Dynamic Array Formulas
    In This Topic

    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:

    UNIQUE Function

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

    For instance - The 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:

    Unique function 

    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 product as "Apple" will be displayed.

    Filter function 

    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, minimum and maximum values and indicate whether to return integers or decimal values.

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

    RANDARRAY function

    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- The cell D4 contains the formula "=SORT(A4:A15)" and returns the customer names sorted in the increasing order.

    SQRT function 

    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 the column C4 or you can also combine both the functions SORT and UNIQUE into a single formula.

    For instance, the 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.

    Sorting unique values in the range

    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.

    SORTBY function

    SEQUENCE Function

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

    For instance - The 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 upto 20.

    SEQUENCE function

    SINGLE Function

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

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

    SINGLE function

    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 .

    JavaScript
    Copy Code 
    // Enable Dynamic Array
spread.options.allowDynamicArray = true;

     

    Using Code

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

    JavaScript
    Copy Code 
    // 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);

     

     

    Copyright © 2020 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page