This video is part 2 in a series of videos on matrix functions in excel. In excel matrices
are referred to as arrays. Excel contains several built in functions to assist in array
calculations. These built in function can calculate matrix multiplication, transpose,
inverse, and determinant. I am going to do an example for each of these. Going over to
my spreadsheet first we are going to have an example of matrix multiplication. For this
example I have two matrices. Matrix "A" and matrix "B". Matrix "A" is a 3 by 3 matrix
and matrix "B" is a 3 by 2 matrix. With matrix multiplication the inner dimensions of the
multiplications must agree. For example there is only one way that I can multiply the two
of these and that is "A" times "B". Here you can see that matrix "A" being a 3 by 3 and
matrix "B" being a 3 by 2 if multiplied in "A" times "B" order the inner dimensions are
3 and 3 and then the outer dimensions are 3 and 2. This means that when they are multiplied
we have our final dimensions which will end up being 3 by 2 since those are the outer
dimensions of the original two matrices. We need to highlight out the area of this new
matrix which is a 3 by 2. For matrix multiplication there is a specific function which "= mmult(A,B)" then control shift enter.
There is the multiplication of the two matrices. Something to be cautious of though. If for
example you highlight out an area that is too wide and press control shift enter the
extra areas will be filled with these symbols showing that there is nothing to be put there.
Also if you highlight an area that is too small you will get the part of the matrix
that is filled in but the rest of it is missing. This warning also applies if you are only
pressing enter rather than control shift enter. It will only fill in the first cell rather
than the entire matrix. Our next example is over transpose. In excel you calculate a matrix
transpose without using a built in function. What you need to do for this is highlight
the range of cells and then copy and then paste special and it is this button here which
is transpose. As you can see the formatting doesn't shift but you end up with 3 17 1 12.
There is a built in function to do this.Here you need to highlight the range of cells of
the answer matrix which is the same as the given one since they are both 2 by 2. Type
is "= transpose(B14:C15)" and then control shift and enter. Even though it is the same
size you can tell that this row 2 5 has become this column of 2 5. Next we are going to go
over the inverse of a matrix. Inverting matrix is similar to flipping a number upside down.
For example 1/2 is the inverse of the number 2. To calculate the inverse of a matrix you
highlight out the area of the matrix which must always be a square matrix and you enter
"= minverse(B6:D8)" and then control shift enter. This has become the inverse of this.
What I am going to do is I am going to change the name to "invs_A" and I am going to perform
a calculation to double check. If you multiply a matrix by its inverse you should always
return with the identity matrix which is given here. I am going to perform that calculation.
Here I am going to highlight out the area and type "=mmult(B6:D8,invs_A)" then control
shit and enter. As you can see it is not exactly 0. But it is pretty close to 0 and for our
purposes it is close enough since there will always be rounding errors. The final example
I am going to give is the matrix determinant. The determinant of a matrix is essentially
a single number value that represents the entire matrix. The interesting thing with
determinants is that if you end up with a determinant of 0 that means the matrix you
are working with is singular so there could be a repeat row in there or the numbers go
too much in order. If the matrix is singular you cannot calculate the inverse of it. As
with the inverse to calculate the determinant you must have a square matrix. In this case
our matrix is a 3 by 3 matrix. To calculate the determinant you type "=mdeterm(B6:D8)".
In this case the determinant of this matrix is 2519. When you calculate the determinant
you do not have to press control shift enter. You can just press enter. I am going to create
a couple more examples to show the determinant. I have two more matrices on this spreadsheet.
First is matrix "B" which goes sequentially from 1 to 9 and the next is matrix "C" which
has a repeat row in the fourth row. I am going to calculate the determinant of both of these
to prove that they are singular. Here although it is not exactly 0 is very close to 0 showing
that this is a singular matrix. if we change this last value to 8 however this goes to
3 and it is no longer singular. Here we have a perfect 0 for our determinant. This is also
singular. To show here if I do inverse of "C" and I calculate the inverse of this array
I get an error because since it is singular I cannot get the inverse however, if I were
to change even one number on this row it is no longer singular and I can now have an inverse.