Array Formulas and Matrix Operations in LibreOffice Calc

LibreOffice Calc provides robust built-in support for array formulas and matrix mathematics, allowing users to perform complex, multi-cell calculations simultaneously. This guide details how array formulas function in Calc, the standard methods for executing them, and the specific matrix functions available for linear algebra and multi-dimensional data analysis.

Working with Array Formulas

An array formula executes operations on multiple values rather than a single value. Instead of returning a single result to one cell, an array formula can return an array of results distributed across a defined block of cells, or condense an array of values into a single summary output.

To apply a standard array formula in LibreOffice Calc: 1. Select the cell or range of cells where the output should appear. 2. Enter the formula. 3. Press Ctrl + Shift + Enter (instead of standard Enter).

Calc automatically wraps the formula in curly braces {} to indicate that it is being processed as an array. Modifying an array range requires selecting the entire resulting block or adjusting the formula across the array.

Core Matrix Functions

LibreOffice Calc adheres to the OASIS OpenFormula standard and includes dedicated functions for linear algebra and matrix arithmetic:

• MMULT(Array1, Array2): Calculates the matrix product of two arrays. The number of columns in Array1 must equal the number of rows in Array2. Must be entered as an array formula across the target dimensions.
• MINVERSE(Array): Computes the inverse of a square matrix. The matrix must have an equal number of rows and columns and a non-zero determinant.
• MDETERM(Array): Returns the determinant of a square matrix. Unlike MINVERSE, this outputs a single scalar value.
• TRANSPOSE(Array): Transposes the rows and columns of an array, converting a horizontal array to a vertical array or flipping an \(m \times n\) matrix into an \(n \times m\) matrix.
• MUNIT(Dimension): Generates an identity (unit) matrix of the specified square dimension.

Statistical and Analytical Array Functions

Calc provides advanced array-based functions commonly used in statistical modeling, regression analysis, and data binning:

• SUMPRODUCT(Array1, Array2, ...): Multiplies corresponding elements in the given arrays and returns the sum of those products. This function natively handles array calculations without requiring Ctrl + Shift + Enter.
• FREQUENCY(DataArray, ClassesArray): Calculates how often values occur within a range of values (bins), returning a vertical array of frequencies.
• LINEST(KnownY, KnownX, LinearType, Stats): Calculates the statistics for a straight line that best fits the data using the least squares method, returning an array containing slopes, intercepts, and standard errors.
• LOGEST(KnownY, KnownX, FunctionType, Stats): Computes an exponential curve fit for data, returning regression coefficients as an array.
• TREND(KnownY, KnownX, NewX, LinearType): Returns values along a linear trend based on least squares fitting across an array of inputs.
• GROWTH(KnownY, KnownX, NewX, FunctionType): Calculates predicted exponential growth across an array of values.

Array Math and Logical Operations

Standard mathematical and logical operators in LibreOffice Calc can be combined into custom array expressions:

• Element-Wise Arithmetic: Expressions like {=A1:A5 * B1:B5} multiply each corresponding pair of cells individually.
• Conditional Array Calculations: Combining logical functions such as IF inside aggregation functions allows multi-criteria evaluations, such as {=SUM(IF((A1:A10="Category")*(B1:B10>50), C1:C10, 0))}.