Week 7

Topics

There is no new mathematics material this week. You will be required to use Excel to do matrix calculations and solve linear systems of equations in Excel HW #2. We provide Excel Tutorials to help you.

Excel Tutorial 2 (Spreadsheet,Video): Matrix Operations

Array Formulas

An array is the same as a matrix: a rectangular block of numbers. Mathematicians call it matrix, computer scientists call it array.

An array formula in Excel is a formula that returns a matrix as a result. The array formulas we use in this class are

• mmult = matrix multiplication
• minverse = matrix inverse
• +, -, * = matrix addition, matrix subtraction, number * matrix
• mtranspose = matrix transpose (in section 6-3).

You enter array formulas the same way you enter other formulas, except for two things:

• You have to select the area where the result goes before you start typing.
• When you are done, you have to hit on a PC, or on a Mac (the command key on a Mac is that loop-de-loop thing next to the half-eaten apple).

If you forget either one of those things, there is nothing you can do except start over with entering the formula.

Example: Suppose you want to multiply a 2x2 matrix A, stored in A1:B2, with a 2x2 matrix B stored in D1:E2. You want to store the result in G1:H2. You need to do the following:

• Select the output area G1:H2
• Type in the formula: =mmult(A1:B2,D1:E2)
• Hit on a PC, or on a Mac

Caution: Excel lets you calculate =A1:B2*D1:E2, but that is not the matrix product. That is the element-by-element product (C11 = A11*B11, instead of C11 = A11*B11+A12*B21).

Example: Suppose you want to compute 2A+3B instead, and store it in G1:H2 again. You do the following:

• Select G1:H2
• Type in the formula: =2*A1:B2+3*D1:E2
• Hit on a PC, or on a Mac

Example: Suppose you want to find the inverse of a 3x3 matrix A stored in A1:C3. (You can only find the inverse of a square matrix). The inverse is again of size 3x3.

• Select a 3x3 space to hold the result, for example D1:F3
• Type in the formula: =minverse(A1:C3)
• Hit on a PC, or on a Mac

Solving Linear Systems

A system of linear equations is written in matrix notation as Ax=b. A is the matrix of coefficients, x is the vector of unknowns, b is the vector of right-hand sides. Formally, the solution is x = A-1*b. That is the way Excel solves equations.

Suppose you want to solve the system

3x + 4y = 1
x - 2y = 7

You type in the matrix somewhere (for example in A2:B3), likewise for the right-hand side (for example in D2:D3). Then you need to

• Select a 2x1 space to hold the result, for example F2:F3
• Type in the formula: =mmult(minverse(A2:B3),D2:D3)
• Hit on a PC, or on a Mac.

The result (3,-2) shows up in F2:F3.

Note: a couple of the problems say "solve by Gauss-Jordan Elimination". Just ignore that, and solve the equations by whatever method you prefer.

Assignments

• Go through the Excel Tutorial 2 spreadsheet or watch the video.
• Complete Excel HW 2
• If you did not take the Exam Policies Homework before Exam 1, you need to take it now and pass with a 100% score.
• Take Practice Exam 2
• Review the Exam Policies if necessary, and take Exam 2. The exam has a time limit of one hour.

Last Updated: Wednesday, August 5, 2015