|
![]() |
|||
MatricesA 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 ArithmeticAs 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.
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
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).
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
|
||||
Contact Leo Wibberly at ldwibber@vcu.edu or (804) 740-4650 to make appointments |