Matrices
A matrix in Excel is sometimes referred to as an array, and consists of a contiguous block of cells. A matrix can be referred to using the notation for a range of addresses such as A1:E5. The address before the colon is the upper left corner of the block, and the address after the colon is the lower right corner of the block.
Matrix Arithmetic
As mentioned before, there are a number of calculations in Excel that involve whole ranges of cells (such as the Sum( ) function or the SumProduct( ) function). However, so far we have only discussed those calculations that result in a single value (otherwise know as "scalar"). There is another class of operations in Excel that produce multiple results. These result either from doing basic arithmetic on groups of numbers or using one of various array functions.
For
much of the basic arithmetic this is mostly a shortcut to avoid repeating
the same calculation for a whole range of data (what we normally use the
autofill operation to do). For instance, if we wanted to add every number
in column A to the corresponding number in column B to produce a third
column C, instead of adding the first two numbers and duplicating the
calculation, we can actually add the entire range. The thing to remember
is that we must first select the appropriate sized column to put the result
in and then finish entering the calculation with a CTRL-SHIFT-ENTER key
combination. The result is an array (or matrix) calculation, designated
with backets. This only works if the A column and the B column are the
same size, and also the region selected for the result. In fact A, B,
and C don't even have to be single columns, they can be entire matrices.
What we have here is matrix addition. The same thing can be done with
subtraction. A similar calculation {=A1*B1:B5} can be used to take a single
number and multiply it by a whole range of values, provided the result
is placed in a range of the same dimensions. This corresponds to scalar-matrix
multiplication.
We can multiply one column, or more generally a matrix, by another matrix of the same dimension in the same way. While this sometimes is useful for accounting chores like billing invoices or hourly wages, it does not technically qualify as matrix multiplication.
Matrix Functions
True
matrix-matrix multiplication is defined somewhat counterintuitively by
multiplying rows by columns and adding the results. This more complicated
procedure is where a couple of specially defined functions come in, MINVERSE,
and MMULT.
MMULT will take an m row, n column range of cells and multiply it by
a separate n row, p column range of cells, placing the result in an m
by p range of cells. You can manually type in =MMULT(?, ?) where the question
marks represent the address ranges of the two matrices, or you can select
it from the list of functions where it will prompt you for the two matrices
(referred to as arrays). You must remember, instead of clicking okay or
ENTER, to use the CTRL-SHIFT-ENTER combination, this is what makes it
an array calculation and displays the brackets you see in the formula
bar. Also important is that BEFORE you type in the formula you must select
the correct sized region for the result (which requires knowing what the
correct size is).
MINVERSE,
while requiring more behind-the-scenes computation, is actually somewhat
simpler to execute. Its argument (inside the parentheses) is a single
square (equal rows and columns) matrix, and its result is another square
matrix of the same size. According to the definition of the inverse of
a matrix, when the original matrix is multiplied by its inverse, the result
is an identity matrix. This is demonstrated on the left. Although not
explicitly stated anywhere, the lower matrix is the product of the two
above it.
A combination of matrix inverse and matrix multiplication can be used
to solve any system of equations where the number of independent equations
is equal to the number of variables (as will be demonstrated in one of
the sample problems below). However, it isn't always apparent whether
one of the equations is a duplicate of or combination of the earlier ones.
When that happens, an attempt to calculate the inverse results in an error,
because there IS no inverse. Sometimes this means there is no solution
to the system of equations but it also might mean there are infinitely
many possible solutions. Traditionally the approach used to find this
family of solutions is to do Gauss Jordan elimination on the augmented
matrix. While any graphing calculator worth its salt has a routine (called
"rref") to accomplish just this, Excel does not. To download
a spreadsheet that will do Gauss Jordan elimination on 3 equations with
3 variables, click here.
One other function that is useful, and a bit hard to find since it is listed under the Lookup and Reference category, is the TRANSPOSE( ). What it does is take a matrix of any dimension and flip it diagonally. In other words, it converts rows to columns and columns to rows. Like MINVERSE, you must select a region apart from the original matrix to put the result, but in this case the dimension of the result is the reverse of the original - so a 2x3 matrix will become a 3x2, etc. The arguments consist of only one matrix and you must still press CTRL-SHIFT-ENTER upon completion.
Some Sample Exercises
Do
the matrix calculation A-3*I where A is the matrix highlighted in yellow
and I is the identity matrix in orange.
The
two lines in the last set of problems can have their equations rearranged
to look like -2x+y=1 and x+2y=6. This can be written in matrix form
as AX=B where A is the yellow matrix and B is the orange one. The solution
can be found as X=A-1B (the inverse of A multiplied
by B - be careful which order you multiply in). Calculate the solution
using matrix functions.- Let A be a single vertical column matrix with the numbers {3,1,4}. Calculate AAT (A multiplied by the transpose of A - once again, remember to do this in the correct order). What is the inverse of the result?
 ...Previous
Page |
Contact Leo Wibberly at ldwibber@vcu.edu or (804) 740-4650 to make appointments