« Basic Functions: SUM, AVERAGE | Main | Importing Tabular Data : CSV Files »
October 11, 2004
Array Formulas: Working with Matrices
An array is a linked range of cells on a spreadsheet containing values. A formula in which the individual values in a cell range are evaluated is referred to as an array formula.
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.
The OOoCalc functions that are introduced here are TRANSPOSE, MMULT, and MINVERSE.
As an introduction to array formulas - we introduce one of the builtin array functions - TRANSPOSE, which as the same suggests - transposes the rows and columns of the original array to the target array.
In the example below, we wish to transpose A1:D3 to B6:D9.
With B6 selected - enter =TRANSPOSE(A1:D3) - followed by Ctrl-Shift-Enter. OOo Calc takes care of the rest! Note that in the Input Window - the function is embedded within { } indicating an array function. These are automatically added by OOo Calc.
OOo Calc also has a builtin function for multiplying matrices - MMULT. The use of this function is demonstrated in the example below.
We use Insert->Names->Define to define the two product matrices as Matrix1 and Matrix2. Using defined names to define and manipulate matrices is good practice - as is highlighting the matrices with different backgorund colors.
To calculate the inverse of a matrix, we use the MINVERSE function. This is illustrated below.
We can now apply our knowledge of matrix manipulation in OOo Calc to solve for a system of linear equations. The technique is illustrated in the example below.
Posted by Dave at October 11, 2004 05:00 AM
Comments
Thank you very much, your example make me more understand for the matrix inverse function, but if the matrix size are 60 by 60 or more, How I can expand the limitation of the minverse array?
Posted by: Parnuwat Gururatana at April 25, 2005 06:49 PM