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:
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:
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:
One-dimensional vertical arrays, formed by cell data populating a single column:
Two-dimensional arrays, formed by cell data in multiple rows and columns.
For detailed information on practical aspects of array formula usage, refer to the following topic: How to Work with Array Formulas.