Get it on Google Play Get it on Apple Store

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