Array Formulas
- 4 minutes to read
An Array formula executes operations on one or more sets of values, known as array arguments. Each array argument must contain the same number of rows and columns. The outcome of an array formula can be either a single value or multiple values.
Array arguments can be cell ranges or array constants.
Array formulas can return single or multiple values:
- Single return value. Such array formulas generally process an array and aggregate it with functions like SUM, COUNT, or AVERAGE. A cell linked to the formula displays the result as its value.
- Multiple return values. If the formula does not include an aggregate function, the result is calculated for every cell within the parameter array. Return values are distributed among multiple cells (mirroring the input array’s layout).
The following image uses an array formula to calculate the value length in cells B3, C3, D3, and E3. Although you specify the formula for a single cell, the returned results appear within multiple cells, matching the input array’s layout.
Individual cells or groups of cells within the array formula range remain read-only. Any attempt to change a cell value directly (without modifying the formula) results in an exception. You can, however, change cell styles: colors, fonts, and so on.
SpreadsheetControl supports two types of array formulas: static (legacy) and dynamic.
- Static (Legacy) Array Formulas
Legacy array formulas are those used in Microsoft Excel 2019 and in earlier versions. These formulas remain static and must be entered into a range that matches the output size. If the number of returned values surpasses the cell count, the formula does not display excess values. If the cell count outnumbers return values, the formula fills extra cells with repeated values.
Refer to the following topic for information on how to organize static array formulas in Spreadsheet Document API: How to: Manage Static (Legacy) Array Formulas
- Dynamic Array Formulas
Dynamic array formulas are a newer feature available in Excel 2019 and later versions. These formulas automatically determine output cell range based on the returned result. If data changes, the formula output refreshes automatically.
The output range of a dynamic array formula is known as the spill range, with the first cell containing the formula. The
#SPILL!error indicates obstacles within the spill range, such as blocking data.
Create Dynamic Array Formulas
The CellRange.DynamicArrayFormula property allows you to specify the array formula for a range of cells. Use the CellRange.DynamicArrayFormulaInvariant property to set the array formula in invariant culture. The range with the same array formula acts as a single entity. You may only modify data for the entire range.
The Worksheet.DynamicArrayFormulas property retrieves the collection of dynamic array formulas (DynamicArrayFormula objects) in the worksheet. You can detect the Range of cells containing the array formula, and the formula text (the DynamicArrayFormula.Formula property).
The following code snippet creates a LEN (returns the number of characters in a text string) dynamic array formula:
using DevExpress.Spreadsheet;
Worksheet worksheet = spreadsheet.Document.Worksheets.ActiveWorksheet;
// Insert dynamic array formulas
worksheet["A1"].DynamicArrayFormulaInvariant = "={\"Red\",\"Green\",\"Orange\",\"Blue\"}";
worksheet.DynamicArrayFormulas.Add(worksheet["A2"], "=LEN(A1:D1)");
Modify Dynamic Array Formulas
Modify array formulas only for the entire range. The CellRange.HasDynamicArrayFormula allows you to identify whether the cell range contains the dynamic array formula. To change the formula, obtain its range with the use of one of the following members:
- Cell.GetDynamicArrayFormulaRange method call;
- DynamicArrayFormula.Range property;
- Worksheet.Range property.
Use the CellRange.DynamicArrayFormula property to manage the dynamic array formula for the obtained range.
The following code snippet detects the formula in the specific cell and creates a new range with the obtained formula:
//...
// delete the array formula
// and create a new range with the same formula.
if (worksheet.Cells["C2"].HasDynamicArrayFormula)
{
string af = worksheet.Cells["C2"].DynamicArrayFormula;
worksheet.Cells["C2"].GetDynamicArrayFormulaRange().DynamicArrayFormula = string.Empty;
worksheet.Range["C2:C11"].DynamicArrayFormula = af;
}
Delete Dynamic Array Formulas
To delete an array formula, call the DynamicArrayFormulaCollection.Remove method. You can remove a specific formula or a formula applied to the specified cell range.
The DynamicArrayFormulaCollection.Clear() method allows you to remove all dynamic array formulas.
Tip
You can also assign an empty string as the CellRange.DynamicArrayFormula value to remove this formula from a cell.
// Clear dynamic array formulas
Cell cell = worksheet.Cells["B2"];
if (cell.HasDynamicArrayFormula) {
CellRange dynamicArrayRange = cell.GetDynamicArrayFormulaRange();
dynamicArrayRange.Clear();
}
// Remove the formula from a specific range
worksheet.DynamicArrayFormulas.Remove(worksheet.Cells["A1"].GetDynamicArrayFormulaRange());