Documents for Excel .NET Edition | GCDocuments
Features / Formulas / Dynamic Array Formulas
In This Topic
    Dynamic Array Formulas
    In This Topic

    Dynamic Array Formulas are the formulas which return multiple values (in an array) to a range of cells on a worksheet. The neighbouring cells are hence populated with the results (calculated data) based on a single formula entered in one cell. This behavior is called 'Spilling' and the range in which the results appear is called a 'Spill Range'. The spill range operator (#) can be used to reference the entire spill range.

    GcExcel supports using dynamic array formulas in worksheets by using the AllowDynamicArray property of the IWorkbook interface. The property when set to true enables the use of dynamic formulas. You can also use CalcError enumeration which specifies the type of calculation error:

    The below dynamic array functions are added in GcExcel:

    Function Category Description
    FILTER Lookup and reference Filters a range of data based on the defined criteria
    RANDARRAY Math and trigonometry Returns an array of random numbers between 0 and 1
    SEQUENCE Math and trigonometry Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
    SINGLE Lookup and reference Returns a single value using logic known as implicit intersection
    SORT Lookup and reference Sorts the contents of a range or array
    SORTBY Lookup and reference Sorts the contents of a range or array based on the values in a corresponding range or array
    UNIQUE Lookup and reference Returns a list of unique values in a list or range 

    Refer to the following example code to enable dynamic array formula and use FILTER function by specifying a criteria

    C#
    Copy Code
    //create a new workbook
    var workbook = new Workbook();
    
    workbook.AllowDynamicArray = true;
    var sheet = workbook.Worksheets[0];
    sheet.Name = "FILTER";
    sheet.Range["A1"].Value = "The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])";
    
    sheet.Range["B3:E19"].Value = new object[,] {
        { "Region", "Sales Rep", "Product", "Units" },
        { "East", "Tom", "Apple", 6380 },
        { "West", "Fred", "Grape", 5619 },
        { "North ", "Amy", "Pear", 4565 },
        { "South", "Sal", "Banana", 5323 },
        { "East", "Fritz", "Apple", 4394 },
        { "West", "Sravan", "Grape", 7195 },
        { "North ", "Xi", "Pear", 5231 },
        { "South", "Hector", "Banana", 2427 },
        { "East", "Tom", "Banana", 4213 },
        { "West", "Fred", "Pear", 3239 },
        { "North ", "Amy", "Grape", 6420 },
        { "South", "Sal", "Apple", 1310 },
        { "East", "Fritz", "Banana", 6274 },
        { "West", "Sravan", "Pear", 4894 },
        { "North ", "Xi", "Grape", 7580 },
        { "South", "Hector", "Apple", 9814 } };
    
    sheet.Range["G3:L4"].Value = new object[,] { { "Criterion", "", "Product", "Units", "", "Total:" }, { 5000, null, null,null,null,null } };
    
    sheet.Range["I4"].Formula = "=FILTER(D4:E19,E4:E19>G4,\"\")";
    sheet.Range["L4"].Formula = "=SUM(IF(E4:E19>G4,1,0))";
    
    sheet.Range["E4:E19,G4,J4:J12"].NumberFormat = "#,##0";
            
    //save to an excel file
    workbook.Save("filterfunction.xlsx");

    The below image shows the output of above code where Filter function is applied in cell I4.

    Dynamic Array formula Filter Function