You should all be familiar with solving things like the example shown here. Let's say we
have a function f(x)=sqrt(x)/(1-ln(x)). We could easily do this in Excel, so let's say
I have x and f(x). I am going to go x from 1 to 10. OK I type in my functions, I take
that and I drag it down. Another option, I am just going to delete that, I am going to
come over here and I am going to name this the vector x, so you can actually name ranges
of cells, not just individual cells, so I am going to name that x, and you'll notice
that when I highlight that it's named x. Now to do the array formulas you just highlight
the resultant, and I am just going to type in the formula in cell C2 with everything
highlighted. Now be careful, you don't just press enter, you press Ctrl+Shift+Enter, and
we auto-fill the formula down, and if I click into any one of these you can see that the
formula bar it's in brackets up here, meaning it was calculated using an array formula.
One formula alone controls the calculation. Not a lot of people actually use this for
something as basic as this, but never the less it's something to keep in mind. A lot
of times we want to add vectors or do math on vectors in arrays, and so these vector
matrix calculations in Excel can be pretty useful. So for example if we wanted to add
these two vectors, A and B, we can name those two ranges, the first one A, the second one
B, and then in column I I could highlight cells I2 to I5, type in the formula =a+b,
then I could do Ctrl+Shift+Enter, and that will add those two vectors. There are a lot
of other matrix functions in Excel ,we have the MMULT, which is matrix multiplication, the
MINVERSE, which is the inverse of a matrix, MDETERM, that's the determinant, and TRANSPOSE.
They all require the use of array formulas and then Ctrl+Shift+Enter. So just as an example
if we have these two matrices that are multiplied together you're multiplying the first component
of a row by the first component of a column, OK. So for example that first element is going
to be a(11)b(11)+a(12)b(21)+a(13)b(31). Whenever you multiply two arrays like this the inner
dimensions must agree, so we can multiply 2x3 by 3x2, and you end up with the outer dimensions.
In general an nxk times a kxm gives an nxm. Most common application for these array formulas
is solving a set of linear equations, so for example we have three linear equations and
three different variables, we have three unknowns and three equations, we can always solve these.
There's and easy way to do this, we can set up an array with the coefficients times the
vector x, and then we can set up a coefficient vector. So a lot of times in math and science
when we're trying to determine the unknowns we can set this up as arrays and vectors.
If you name this coefficient array A, we have our x vector of our unknowns, and then our
b which is the resultant vector, we can write this as A*x=b. Now what we're trying to find
is x here, OK, we know A, we know b, we're trying to find x. If you multiply both sides
by the inverse of A any array times its inverse is always equal to the identity matrix, which
is essentially just 1. So we end up with x=A^-1*B, and that will give us what the values of x(1),
x(2), and x(3) are. Let's go ahead and do this in Excel. I am going to set up our coefficient
array. This is equivalent to A, so I am going to label that A. I have b, we're going to
label that b. So if we want to find x it's going to be the matrix inverse, which is MINVERSE
of A times b. So I am going to just put a title, a label in here for x, I am doing matrix
multiplication. I know that the resultant vector x is going to be a 3x1 because I am
multiplying a 3x3 by a 3x1, and I result in a 3x1. So I am going to highlight those three
cells, I am going to type in a formula, now what I am doing is I am multiplying the inverse
of A times b, so that function is mmult, matrix multiplication, I am multiplying now the inverse
of A times b. So there's a built in function for the inverse of A which is minverse, so
minverse(A)*b. I am going to press Ctrl+Shift+Enter. So these are the solutions for x(1), x(2),
and x(3) in our original set of equations. So this is a pretty straightforward way of
using matrix multiplication in Excel.