Array Formulas

• Jan 11, 2021
• 5 minutes to read

What is an Array Formula

An Array formula is a formula that performs actions on two or more sets of values, which are called array arguments. Each array argument must have the same number of rows and columns. The result of an array formula can be either a single value or multiple values.

Array arguments can be cell ranges or array constants.

An array formula that returns a single value typically processes a series of data, and aggregates it using the SUM, COUNT or AVERAGE functions. The resulting value is written into a cell to which the formula belongs. Usually, an array formula returns a series of data. Returned values are distributed among the cells of the array formula range. If the number of values exceeds the number of cells, excessive values are not shown. If the number of cells is greater than the number of values, excessive cells are not left blank - they are filled with the same values repeatedly.

An individual cell or group of cells that is part of the array formula range is read-only. Any attempt to change the cell value results in an exception. An array formula range only allows you to change the formula for the entire range. However, you can change cell formatting (color, font, etc.) for each cell individually.

How to Create an Array Formula

This example demonstrates how to create an array formula.

C#

// Create an array formula that multiplies values contained in the cell range A2 through A11 
// by the corresponding cells in the range B2 through B11, 
// and displays the results in cells C2 through C11.
worksheet.Range["C2:C11"].ArrayFormula = "=A2:A11*B2:B11";

// Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
// and displays the results in cells D2 through D11.
worksheet.Range["D2:D11"].ArrayFormula = "=C2:C11*2";

// Create an array formula that multiplies values contained in the cell range B2 through D11, 
// adds the results, and displays the total sum in cell D12.
worksheet.Cells["D12"].ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)";

// Re-dimension an array formula range:
// delete the array formula and create a new range with the same formula.
if (worksheet.Cells["C13"].HasArrayFormula) {
    string af = worksheet.Cells["C13"].ArrayFormula;
    worksheet.Cells["C13"].GetArrayFormulaRange().ArrayFormula = string.Empty;
    worksheet.Range["C2:C11"].ArrayFormula = af;
}

VB.NET

' Create an array formula that multiplies values contained in the cell range A2 through A11 
' by the corresponding cells in the range B2 through B11, 
' and displays the results in cells C2 through C11.
worksheet.Range("C2:C11").ArrayFormula = "=A2:A11*B2:B11"

' Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
' and displays the results in cells D2 through D11.
worksheet.Range("D2:D11").ArrayFormula = "=C2:C11*2"

' Create an array formula that multiplies values contained in the cell range B2 through D11, 
' adds the results, and displays the total sum in cell D12.
worksheet.Cells("D12").ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)"

' Re-dimension an array formula range:
' delete the array formula and create a new range with the same formula.
If worksheet.Cells("C13").HasArrayFormula Then
    Dim af As String = worksheet.Cells("C13").ArrayFormula
    worksheet.Cells("C13").GetArrayFormulaRange().ArrayFormula = String.Empty
    worksheet.Range("C2:C11").ArrayFormula = af
End If

The CellRange object provides the CellRange.ArrayFormula property to specify the array formula for a range of cells. The range of cells containing the same array formula is treated as a single entity. Thus, you can only modify data for the entire range.

To determine the array formula range, use the Cell.GetArrayFormulaRange method. If a cell is not a part of the range specified by the array formula, this method returns null.

All array formulas in a worksheet are accessible via the Worksheet.ArrayFormulas property that returns the ArrayFormulaCollection object. This object is a collection of ArrayFormula type items. Therefore, you can easily determine the ArrayFormula.Range of cells that contain the array formula, as well as the text of the ArrayFormula.Formula itself.

How to Modify an Array Formula

Array formulas can be modified for the entire formula range only. To change the formula, get its range via the Cell.GetArrayFormulaRange method or the ArrayFormula.Range property. You can also access the required range using the Worksheet.Range property. The CellRange.ArrayFormula property will allow you to get or set the array formula for the created range.

For each cell that belongs to the array formula range, the CellRange.ArrayFormula property returns a formula string, and throws a System.InvalidOperationException when trying to set its value.

To check whether the cell is the top left cell in the range, use the Cell.IsTopLeftCellInArrayFormulaRange property.

Cells have the CellRange.Formula property, which gets or sets ordinary formulas. If a cell belongs to the array formula range, the CellRange.Formula property returns the same string as the CellRange.ArrayFormula property. An attempt to set the Formula property for any cell(s) in the array formula range results in an exception indicating that a portion of the array cannot be changed.

How to Delete an Array Formula

To delete an array formula, assign an empty string to the CellRange.ArrayFormula property of the entire array formula range.