# Matrix Operations in Excel

**4. Matrix Operations in Excel .**

Matrix Manipulations: Vectors, Matrices, and Arrays. How Excel Handles

Matrix Math. Basic Matrix Operations. Solving Systems of Linear Equations .

**Matrix Manipulations: Vectors, Matrices, and Arrays.**

In this section we consider the topic of Vectors, Matrices and Arrays and

their application in solving Linear Equations and other linear algebra

problems.

Simultaneous linear equations occur frequently in engineering in such areas

as heat conduction, molecular diffusion, fluid mechanics and in data

regression. Excel’s “Solver” feature will be used in a later chapter to solve

more complicated linear and nonlinear systems of equations.

Generally the term matrix (from mathematics) and array (from Excel) can

be used interchangeably to refer to data organized in row and column

fashion. Matrices consisting of a single row or a single column are called

vectors. Even though the functions are “named” with matrix there is no

help in Excel under “matrix” only “array”.

Typical Linear Equation Set and Corresponding Matrices

Is represented in math as

[A][X] = [B]

and has the solution [X] = [A]^{-1}[B]

**How Excel Handles Matrix Math.**

Matrix operations are handled in two different fashions in Excel. Addition of

matrices and scalar multiplication are handled by conventional cell arithmetic

(copying cell formulas) whereas advanced matrix operations such as

transposition, multiplication and inversion are handled by **matrix (array)
functions.**

Key to understanding the use of matrix operations is the concept of the

matrix (array) formula. Such a formula uses matrix functions and returns a

result that can be a matrix, a vector, or a scalar, depending on the

computations involved. **Whatever the result may be, an area on the
spreadsheet of precisely the correct size must be selected before the
formula is typed in (otherwise you will either lose some of the
answer or get added and possibly confusing information).**

After typing such a formula, you "enter" it with three keys pressed at once:

CTRL, SHIFT and ENTER. This indicates that a matrix (array) result really is

desired. It also designates the entire selected range as the desired location

for the answer. To modify or delete the formula, select the entire region

beforehand.

When matrix computations are performed in this way, the "result areas" will

be updated immediately whenever any of the numbers in the "input areas"

change (unless automatic recomputation has been turned off). This can be a

great help when one wishes to evaluate the effects of changes in

assumptions , initial conditions, etc.. This feature, coupled with the ability to

see matrices, complete with identification of the rows and columns (i.e. in

the form that we have termed tables), will often make the spreadsheet

environment the preferred choice for computation, if not for communication.

**Basic Matrix Operations.**

Matrix Addition : [C] = [A] + [B]

Method 1: Corresponding elements will be added using “cutting and

pasting”.

Type the formula =B2+B6 in cell B10 and copy and paste into the cells in

region B10:C12.

Method 2: Matrix operator (+) will be used with “named ranges”.

1. CLEAR AREA “C” FIRST. Highlight and name the cells from B2:C4 as

“A”. This is done by typing “A” in the “name field” of the function

picker.

2. Similarly highlight and name the range B6:C8 as “B”.

3. Highlight the destination range and type the following formula:

=A+B but do not press “enter” instead press “control-shift-enter”

to complete the formula. This will introduce { } characters around the

formula {=A+B} which indicate an array operation. YOU CANNOT

SIMPLY TYPE THE BRACES.

You should see the result in the area highlighted (in green above).

**Matrix Subtraction and Scalar Multiplication.**

You can use either of these methods to subtract (element by element) or

multiply (all elements by the same value). For example:

{=6*A} would produce a new array with all values in A multipled by 6.

**Multiplying Two Matrices.**

Matrix multiplication requires that the two matrices are “conformable” (that

is, appropriate number of rows and columns. The number of columns in the

first matrix must equal the number of rows in the second matrix. That is,

you can multiple A(2,5)xB(5,3) because the “inner” numbers are the same.

The size of the result is governed by the “outer” numbers, in this case (2,3).

This should also suggest that AxB ≠ BxA since the result of AxB would be

C(2,3) and the result of BxA is C(3,2).

To multiply two matrices, use the MMULT function.

=MMULT(first_matrix, second_matrix)

Remember you must highlight the destination matrix BEFORE completing the

formula with Shift-Control-Enter!

**Transposing A Matrices.**

The mathematical operation of “transposing” a matrix is simply to switch the

“rows” with the “columns”. Hence, a row vector’s transpose is a column

vector and the transpose of a 2x3 matrix is a 3x2 matrix.

To take the transpose of a matrix, use the TRANSPOSE function.

**Inverting A Matrices.**

The mathematical operation of “inverting” a matrix requires that two

conditions are met:

1. The matrix must be “square” (same number of rows and columns)

2. The matrix must be “nonsingular”

A matrix is “singular” is any of the following are true:

1. Any row or column contains all zeros

2. Any two rows or columns are identical

3. Any row or column is a linear combination of other rows or columns.

To take the inverse of a matrix, use the MINVERSE function.

**Determinant Of A Matrix.**

The determinant of a matrix is a single value and is often encountered in

solving systems of equations. Only square matrices have a determinant.

Since the calculation of a determinant involves a significant number of

calculations, it is subject to “round-off” error. When the determinant is

essentially “zero” (that is, 1x10-14 or less) it can be considered “zero”.

To find the determinant of a matrix, use the MDETERM function.

Note, the last line is the same as the first line multiplied by -0.5.

**Solving Systems of Linear Equations.**

We now have the necessary tools to solve systems of linear equations.

Here are the steps:

1. Write the equations in matrix form ( coefficient matrix ) x [unknown

vector] = right hand side vector. [A][x] = [b].

2. Invert the coefficient matrix [A]^{-1}

3. Multiply both sides of the equation by the inverted coefficient matrix.

This is the solution matrix .

In the example below, the solution “x” is

=MMULT([A]^{-1},[b])

Prev | Next |