LibreOffice 26.2 Help
This category contains the array functions.
An array is a linked range of cells on a spreadsheet containing values. A square range of 3 rows and 3 columns is a 3 x 3 array:
|
A |
B |
C |
D |
E |
|
|---|---|---|---|---|---|
|
1 |
7 |
31 |
33 |
||
|
2 |
95 |
17 |
2 |
||
|
3 |
5 |
10 |
50 |
||
|
4 |
The smallest possible array is a 1 x 2 or 2 x 1 array with two adjacent cells.
A formula in which the individual values in a cell range are evaluated is referred to as an array formula. The difference between an array formula and other formulas is that the array formula deals with several values simultaneously instead of just one.
Not only can an array formula process several values, but it can also return several values. The results of an array formula is also an array.
To multiply the values in the individual cells by 10 in the above array, you do not need to apply a formula to each individual cell or value. Instead you just need to use a single array formula. Select a range of 3 x 3 cells on another part of the spreadsheet, enter the formula =10*A1:C3 and confirm this entry using the key combination CommandCtrl + Shift + Enter. The result is a 3 x 3 array in which the individual values in the cell range (A1:C3) are multiplied by a factor of 10.
In addition to multiplication, you can also use other operators on the reference range (an array). With LibreOffice Calc, you can add (+), subtract (-), multiply (*), divide (/), use exponents (^), concatenation (&) and comparisons (=, <>, <, >, <=, >=). The operators can be used on each individual value in the cell range and return the result as an array if the array formula was entered.
Comparison operators in an array formula treat empty cells in the same way as in a normal formula, that is, either as zero or as an empty string. For example, if cells A1 and A2 are empty the array formulas {=A1:A2=""} and {=A1:A2=0} will both return a 1 column 2 row array of cells containing TRUE.
Use array formulas if you have to repeat calculations using different values. If you decide to change the calculation method later, you only have to update the array formula. To add an array formula, select the entire array range and then make the required change to the array formula.
Array formulas are also a space saving option when several values must be calculated, since they are not very memory-intensive. In addition, arrays are an essential tool for carrying out complex calculations, because you can have several cell ranges included in your calculations. LibreOffice has different math functions for arrays, such as the MMULT function for multiplying two arrays or the SUMPRODUCT function for calculating the scalar products of two arrays.
You can also create a "normal" formula in which the reference range, such as parameters, indicate an array formula. This formula is also known as "implicit intersection" of array formula. The result is obtained from the intersection of the reference range and the rows or columns in which the formula is found. If there is no intersection or if the range at the intersection covers several rows or columns, a #VALUE! error message appears. The following example illustrates this concept:
In the table above, place the array formula in D1:
{=A1:A3 + 100}
Cells D1, D2, D3 have values 107, 195, 105 respectively.
Insert the formula below in E2, do not enter as array formula.
=A1:A3 + 100