This video is part 1 in a series of videos on matrix math in excel. In excel matrices
are referred to as arrays. In this video I will be going over a couple of different things.
First I will be going over how to name arrays and second I will be going over two methods
for simple array calculations. If I go to my spreadsheet I have 3 matrices here. The
first and second are both 3 by 3 matrices. The third is a 1 by 1 matrix which is also
just a single number. to name an array or matrix you must first highlight the entire
range of cells that makes up the array. Then in the main box you enter the new name. In
this case it will be "A". For the next one it will be "B". For the third one it will
be "C_" because the name "C" is already taken by a built in excel function. Here if I highlight
these I can see "A", "B" and "C_". If I go to the name box and just type in "A" it will
highlight the whole range that is "A". Something useful to do when you are working with excel
matrices is to add formatting to the matrices to make them easier to identify. For example
one method would be to add a border around them to box in the matrix. Another would be
to color. I could color this pink. You can also do a combination of the two. Some people
to keep it more in line of what matrices look like on paper when they are doing a border
will add one on either side to box it in like a matrix. There are two primary ways for simply
excel array calculations. The first is a brute force method. It uses simple cell referencing.
The second uses built in arrays. I will show you the difference between them. If I go back
to my spreadsheet I am going to delete the sizes here and I am going to create a new
matrix called "D". For this example I am going to add together matrix "A" and matrix "B".
I will first do the brute force method. I will enter equals the first part of "A" plus
the first part of "B". Then using the fill handle I will simply drag and fill in the
rest of the matrix. I am going to go ahead and box in my matrix. It is rather easy to
do. If you select any one of these cells you can see the resulting formula up top that
will show you what it is referencing however, it does not show you the names of the matrix
since it uses only relative referencing. Here I am going to create another matrix called
"E". For this example I am going to be using the built in array functions. I am going to
highlight the range of cells that my answer matrix is going to be and I am going to subtract
the two matrices this times. I type in "= A - B". As I typed the "A" and "B" you can see
the color lines going around each of these arrays to show that "A" is the one with the
blue around it and "B" is the one with the green around it to help you keep track of
what you are referencing. I do not just press enter. If I press enter it will fill in here
and it will not answer it properly. Instead you type control shift enter. It will then
fill in the entire array. If you look up here in the formula bar you can see that it says
equals A - B with a curly bracket on either side. The curly bracket say that this is part
of an array. This single cell is still "A" minus "B" but the curly brackets let you know
that it is part of the larger array function. I am going to also box in this one. This does
not just work for addition and subtraction between matrices. You can also do for both
methods scalar addition, subtraction or multiplication. In this case I will do this cell times the
array "A". Again with control shift and then enter. There is my new matrix. Let's say here
I will do element wise multiplication of that times that and use the fill handle to fill
in the rest. To show you the difference between the two element wise multiplication I can
do "= A * B" which is the same calculation I did over here. Again control shift and then
enter.I still get the same values. Although the two methods work a little bit differently
you still get the same values between both of them. One of my favorite parts about using
array math like this is if I were to update one of these cells such as changing 16 to
20 you can see that it changed all of the cells that referenced it. If I wanted to change
from 7 to 0 watch carefully down here. Here and here changed to 0. This decreased to 14
since it was added to this other matrix. If i want I can change an entire matrix to 0
and see how much these change as well. This way if you update one matrix that is part
of a larger calculation your answer matrices will update as well making sure all of your
calculation stay in order.