Documents for Excel, Java Edition Documentation
Features / Formulas / Set Array Formula
In This Topic
    Set Array Formula
    In This Topic

    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 Java, you can use setFormulaArray method 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 getHasArray method of the IRange interface. In order to get an entire array if specified range is part of an array, you can use getCurrentArray method.

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

    Java
    Copy Code
    // Setting cell value using arrays
    worksheet.getRange("E4:J5").setValue(new Object[][] { { 1, 2, 3 }, { 4, 5, 6 } });
    
    worksheet.getRange("I6:J8").setValue(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.getRange("O9:Q11").setFormulaArray("=E4:G5*I6:J8");
    
    // Verify if Range O9 has array formula.
    if (worksheet.getRange("O9").getHasArray()) {
        
    // Set Range O9's entire array's interior color.
        IRange currentarray = worksheet.getRange("O9").getCurrentArray();
        currentarray.getInterior().setColor(Color.GetGreen());
    }