[]
        
(Showing Draft Content)

Dynamic Array Formulas

Dynamic Array Formulas are the formulas which return multiple values (in an array) to a range of cells on a worksheet. The neighboring 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 dynamic array formulas in worksheets by using the IRange.Formula2 property which allows you to define dynamic array formula in a worksheet. It also lets you specify a formula without automatically adding the intersection operator (@). To enable use of the dynamic array formulas, you need to specify formula of IRange object through the Formula2 property.

Note: In v5.0 release, the AllowDynamicArray property has been obsoleted. The property can currently be used along with IRange.Formula property to support compatibility with v4.2 version. However, we recommend using the new Formula2 property as the AllowDynamicArray property might be removed in future.

You can also use CalcError enumeration which specifies the type of calculation error:

  • Calc: Occurs when calculation engine encounters a scenario it does not currently support.

  • Spill: Occurs when a formula returns multiple results but can't return these values to neighboring cells.

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

Generatesa 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

//create a new workbook
var workbook = new Workbook();

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"].Formula2 = "=FILTER(D4:E19,E4:E19>G4,\"\")";
sheet.Range["L4"].Formula2 = "=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

Note: In addition, GcExcel also supports Array and Text manipulation functions. For more information, see Formula Functions.