LibreOffice Calc Array Functions

Table of Contents

Array Functions

This category contains the array functions.
What is an Array?
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
1	7	31	33
2	95	17	2
3	5	10	50

The smallest possible array is a 1 x 2 or 2 x 1 array with two adjacent cells.

SUMPRODUCT

Multiplies corresponding elements in the given arrays, and returns the sum of those products.
Syntax
SUMPRODUCT(Array1; Array2; …; Array30)
Array1, Array2; …; Array30 represent arrays whose corresponding elements are to be multiplied.
At least one array must be part of the argument list. If only one array is given, all array elements are summed.

Example

	A	B	C	D
1	2	3	4	5
2	6	7	8	9
3	10	11	12	13

=SUMPRODUCT(A1:B3;C1:D3) returns 397.

Calculation: A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3
You can use SUMPRODUCT to calculate the scalar product of two vectors.

MMULT

Calculates the array product of two arrays. The number of columns for array 1 must match the number of rows for array 2. The square array has an equal number of rows and columns.
Syntax
MMULT(Array; Array)
Array at first place represents the first array used in the array product.
Array at second place represents the second array with the same number of rows.

Example

Select a square range. Choose the MMULT function. Select the first Array, then select the second Array. Using Function Wizard, mark the Array check box. Click OK. The output array will appear in the first selected range.

TRANSPOSE

Transposes the rows and columns of an array.
Syntax
TRANSPOSE(Array)
Array represents the array in the spreadsheet that is to be transposed.

Example

In the spreadsheet, select the range in which the transposed array can appear. If the original array has n rows and m columns, your selected range must have at least m rows and n columns. Then enter the formula directly, select the original array and press Shift + Ctrl + Enter. Or, if you are using the Function Wizard, mark the Array check box. The transposed array appears in the selected target range and is protected automatically against changes.

	A	B	C	D
1	2	3	4	5
2	6	7	8	9

The above table is 2 rows, 4 columns. In order to transpose it, you must select 4 rows, 2 columns. Assuming you want to transpose the above table to the range A7:B10 (4 rows, 2 columns) you must select the entire range and then enter the following:
TRANSPOSE(A1:D2)
Then make sure to enter it as matrix formula with Shift + Ctrl + Enter. The result will be as follows: