Excels Builtin Matrix Functions

Performing matrix mathematics with Excel is very simple. Let's begin by assuming that the matrices A and B have been defined by selecting the 3R x 3C arrays of cells containing the values shown in Figure 3-1 and naming them by using Define Name. Remember, we're simply assigning a range name to a range of cells. We usually refer to it as a range or an array; the fact that we are calling it a matrix simply indicates what we intend to do with it.

B

c 1

D

3

Matrix A

4

2

3

4

5

3

2

-1

6

. . .

3

7

F

G

H

3

Matrix 8

4

2

a

2

5

D

3

-3

S

-3

-2

1

Figure 3-1. Ranges of cells defined as A and B. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Figure 3-1. Ranges of cells defined as A and B. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Addition or Subtraction. To add a constant (e.g., 3) to matrix A, simply select a range of cells the same size as the matrix, enter the formula =A+3, then press COMMAND+RETURN or CONTROL+SHIFT+RETURN (Macintosh) or CONTROL+SHIFT+ENTER (Windows). When you "array-enter" a formula by pressing e.g., CONTROL+SHIFT+ENTER, Excel puts braces around the formula, as shown below: {=A+3}

Do not type the braces; if you do, the result will not be recognized by Excel as a formula.

D

E

I

F

8

5

6

7

9

6

5

2

10

7

6

10

J

Figure 3-2. Result matrix {A + 3}. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Figure 3-2. Result matrix {A + 3}. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Subtraction of a constant, multiplication or division by a constant, or addition of two matrices is performed in the same way by using standard Excel algebraic operators.

Scalar Multiplication. Scalar multiplication can be either multiplication of the elements of a matrix by a constant, e.g., a formula such as {=3*A}, or multiplication of corresponding elements of two matrices, e.g., {=A*B}. The result of the latter formula is shown in Figure 3-3.

D

E

F

16

4

0

8

17

0

6

3

18

-12

-6

7

Figure 3-3. Result matrix {A x B}. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheetl')

Figure 3-3. Result matrix {A x B}. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheetl')

Matrix multiplication can be accomplished easily by the use of Excel's worksheet function MMULT(matrixl, matrixZ). For the matrices A and B defined above, entering the formula =MMULT(A,B) yields the result shown in Figure 3-4 while the formula =MMULT(B,A) yields the result shown in Figure 3-5.

D

E ; F j

24

-8

1 -1

25

9

8 -1

26

-13

-5 j 6

Figure 3-4. Result matrix AB. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheetl')

D

E

F

29

-3

-3

22 -24

30

-8

-10

-3

Figure 3-5. Result matrix BA. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Figure 3-5. Result matrix BA. (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheet 1')

Matrix multiplication of two matrices is possible only if the matrices are conformable, that is, if the number of columns of A is equal to the number of rows of B. The opposite condition, if the number of rows of A is equal to the number of columns of B, is not equivalent. The following examples, involving multiplication of a matrix and a vector, illustrate the possibilities:

MMULT (4x3 matrix, 3 x 1 vector) = 3x1 result vector

MMULT (1 x 4 vector, 4x3 matrix) =1x4 result vector In other words, the two inner indices must be the same.

Transposition. The transpose of a matrix may be calculated by using the worksheet function TRANSPOSE(array) or obtained manually by using the Transpose option in the Paste Special... menu command.

The size of the array that can be transposed is limited only by the size of the Excel spreadsheet; the number of rows or columns cannot b£ greater than 256.

Matrix Inversion. The process for inverting a matrix "manually" (i.e., using pencil, paper and calculator) is complicated, but the operation can be carried out readily by using Excel's worksheet function MINVERSE(array). The inverse of the matrix B above is shown in Figure 3-6.

D

E !

F

36

-0.25

-0.33333333

-0.5

37

0.75

0.E666B6B7

0.5

38

0.75

0.33333333

0.5

Figure 3-6. Result matrix B (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheetl')

Figure 3-6. Result matrix B (folder 'Chapter 03 (Matrices) Examples, workbook 'Matrix Math', sheet 'Sheetl')

The size of the matrix must not exceed 52 rows by 52 columns.

Evaluation of the Determinant. The determinant of a matrix of TV rows x N columns can be obtained by using the worksheet function MDETERM(array).

The function returns a single numerical value, not an array, and thus you do not have to use CONTROL+SHIFT+ENTER. The value of the determinant of B, represented by |B|, is 12.

0 -1

Post a comment