Skip to main content

Array Formulas

  • 2 minutes to read

Like Excel, the Spreadsheet and Report Designer controls allow you to carry out simultaneous calculations on homogenous sets of values. Such formula expressions accept arrays as arguments and in some cases, return an array as a result. Hence, the name.

Array formulas offer the following advantages:

Consistency
If you double-click any cell within the result array, you see the same formula;
Safety

You do not need to protect cells containing array formulas to prevent unintentional modifications. Any occasional attempt to modify an array formula raises the EdxSpreadSheetCannotChangePartOfArrayError exception:

An Error Message Example

To modify an array formula assigned to a cell range, select any cell within the range, invoke the cell’s in-place editor, modify the formula expression, and confirm the change by pressing the Ctrl+Shift+Enter key combination as an additional safety measure.

File Size Optimization

You can use a single array formula instead of multiple formula expressions for intermediate calculations and thus reduce the spreadsheet document size. For instance, you can calculate a subtotal in the expense report with a single use of the SUM function’s array variety instead of calculating multiple intermediate sums for each particular column:

An Array Formula Expression

In a worksheet, there are three types of arrays available as both function arguments and returned results:

  • One-dimensional horizontal arrays, formed by cell data populating a single row:

    Horizontal Arrays as Arguments

  • One-dimensional vertical arrays, formed by cell data populating a single column:

    Vertical Arrays as Arguments

  • Two-dimensional arrays, formed by cell data in multiple rows and columns.

    Two-Dimensional Arrays

For detailed information on practical aspects of array formula usage, refer to the following topic: How to Work with Array Formulas.