Set Array Formula

Array formula is a formula that can execute multiple calculations on individual cells or a range of cells to display a column or a row of subtotals. The array formula can consist of array of row of values, column of values or simply a combination of rows and columns of values that may return either multiple results or a single result.

Array formulas can be used to simplify the following tasks in a worksheet:

1. You can count the number of characters in a range of cells.

2. You can sum numeric values in cells that meet a specified criteria. For instance,the highest value in a range or values that fall between an upper and lower boundary.

3. You can sum every nth value in a range of cell values in a spreadsheet.

In GcExcel .NET, you can use FormulaArray property of the IRange interface to set array formula for a range. In case, you want to find out whether a range has array formula or not, you can use the HasArray property of the IRange interface. In order to get an entire array if specified range is part of an array, you can use CurrentArray property.

Refer to the following example code to set array formula and get entire array:

// Setting cell value using arrays
worksheet.Range["E4:J5"].Value = new object[,]
{
    {1, 2, 3},
    {4, 5, 6}
};

worksheet.Range["I6:J8"].Value = new object[,]
{
    {2, 2},
    {3, 3},
    {4, 4}
};
// To set array formula for range.
//O     P      Q
//2     4      #N/A
//12    15     #N/A
//#N/A  #N/A   #N/A
worksheet.Range["O9:Q11"].FormulaArray = "=E4:G5*I6:J8";
            
//O9's current array is "O9:Q11". Current array gets the entire array.
var currentarray = worksheet.Range["O9"].CurrentArray.ToString();