Back to main page

MATRIX OPERATIONS

Matrix operations are needed frequently e.g. in parameter estimation and optimization problems.

E.g. solution of a linear system of equations Ax=b where A is n by n coefficient matrix, x is an unknown n by 1 vector and b is a known n by 1 vector.

(M-1)

Example M-1. Solve the linear system of equations Ax=b using Excel's matrix-operation formulas. Matrix A and vector b as given below.

Solution: x=A^-1b where A^-1 is the inverse on matrix A.
a) Find A^-1 using the MINVERSE-function of EXCEL. The first step is to enter an array formula as shown below.

Note: EXCEL: About array formulas and how to enter them

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas the same way that you create basic, single-value formulas. Select the cell or cells that will contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to enter the formula.

The inverse matrix A^-1 is shown below.

Checking: A^-1A=I where I is the unit matrix (diagonal values 1 and all other values equal to zero). MMULT-function can be used to calculate A^-1A. The result is shown below indicating that A^-1A is a unit matrix (values outside diagonal zero or very small).

b) Solution: calculate x= A^-1b using MMULT-function .

Checking of solution: Ax should be equal to b. The result below shows that Ax=b, i.e. the solution to
x={0.714286 -3.07143 -2.42857 1.357143}^T is correct.