MINVERSE

In Excel, matrix inversion can be performed similarly to matrix transposition (see earlier). Figure 2-13 gives an example. Cells D3 E4, defining the target matrix, have to be pre-selected and now the MINVERSE function is applied to the source cells A3 B4. Finally, the SHIFT+CTRL+ENTER key combination is used to confirm the matrix operation. 

MMULT(MINVERSE(MMULT(TRANSPOSE(C5 E15),C5 E15)),MMULT(TRANS... 

In Figure 19-5, we enter the wavelengths in column A just to keep track of information. We will not use these wavelengths for computation. Enter the products eh for pure X in column B and eh for pure Y in column C. The array in cells B5 C6 is the matrix K. The Excel function MINVERSE(B5 C6) gives the inverse matrix, K-1. The function MMULT(matrix 1, matrix 2) gives the product of two matrices (or a matrix and a vector). The concentration vector, C, is equal to K 1 A, which we get with the single statement... 

To use the template in Figure 19-5, enter the coefficients, eb, measured from the pure compounds, in cells B5 C6. Enter the absorbance of the unknown in cells D5 D6. Highlight cells F5 F6 and type the formula =MMULT(MINVERSE(B5 C6), D5 D6j . Press CONTROL+SHIFT+ENTER on a PC or COMMAND(3 )+RETURN on a Mac. The concentrations [XI and [Y] in the mixture now appear in cells F5 F6. 

Leave these cells selected then type =mmult(minverse(D2 J8),C2 C8) then simultaneously press Ctrl Shift Enter. 

The matrix is solved as described in Appendix H. The procedure is select cell B35, shift-arrow down to cell B41, type mmult(minverse(D22 J28),C22 C28) then simultaneously press Ctrl Shift Enter. This is the only time the matrix must be solved. 

Excel provides functions for the manipulation of arrays or matrices TRANSPOSE(array) returns the transpose of an array, MDETERM(array) returns the matrix determinant of an array, MINVERSE(array) returns the matrix inverse of an array, MMULT(arrayl, array2) returns the matrix product of two arrays and SUMPRODUCT(arrayI, array2,. ..) returns the sum of the products of corresponding array elements. These functions are discussed more fully in Chapter 9. 

If you use a worksheet function within VBA that returns an array, the lower array index will be 1. Such worksheet functions include LI NEST, TRANSPOSE, MINVERSE, MMULT. Other functions that return arrays include the VBA function Caller when used with a menu command or toolbutton. 

Related Functions MINVERSE, MMULT, SUMPRODUCT, TRANSPOSE... 

Array must have an equal number of rows and columns. If any cells in array do not contain numbers, MINVERSE returns VALUE . If MDETERM for the array returns 0, the array cannot be inverted MINVERSE will return NUM error. Example See Chapter 9 for details. 

Related Functions MDETERM, MINVERSE, SUMPRODUCT, TRANSPOSE MOD... 

Two of the functions under Math Trig are MINVERSE and MMULT. These functions have the obvious use. The inverse function, of course, must act on a square matrix, and creates a square matrix. To use it, first create the matrix, as illustrated in cells A1 C3 of Figure A.ll. 

Then in cell A6 type = MINVERSE(A1 C3). You can also insert the Al C3 by selecting the cells. Click on cell A6, press the shift key, and select the other comer of the matrix, C8. Press F2, the Ctrl-Shift-Enter. The inverse appears in A6 C8. The matrix multiplication is illustrated in Figure A. 11 by multiplying these two matrices together the result should be the identity matrix. Click on cell All, type =MMULT(A1 C3,A6 C8). Click on cell All, press the shift key, and select the other corner of the matrix, C13. Press F2, the Ctrl-Shift-Enter. The matrix multiplication appears in Al 1 C13. Indeed it is the identity matrix. 

Spreadsheets are created to facilitate computation. Commonly used mathematical operations (such as SIN, LOG, SQRT, and MINVERSE) are built-in as functions, and some more complicated procedures (e.g., Solver, Random Number Generation, Regression) are provided as macros. However, no spreadsheet maker can anticipate the needs of all possible users, and Excel therefore allows the introduction of so-called user-defined functions and macros. In section 9.2d we will describe some user-defined functions, while chapter 10 deals extensively with user-defined macros. However, beyond the simple exercises of section 10.1, it makes no sense to enter long macros by hand, and they are therefore provided in a web site from which they can be downloaded and stored onto your own computer disk or diskette. The web site also contains a sample file that is, likewise, larger than you might want to enter manually. 

Highlight A13 D 16, then type = MINVERSE(A6 D9), and press Ctrl + Shift + Enter (i.e., hold down the Control and Shift keys while depressing the Enter key), in order to inform the spreadsheet that you intend this formula for the entire block. You will see the inverse matrix appear in that block. 

Determining the inverse of a matrix by hand is a fairly complicated matter. Fortunately, Excel has a built-in function, MINVERSE, that will perform the inversion. It also has a matrix multiplication function, MMULT, that will calculate the product of two matrices. In order to let the spreadsheet know that your instructions concern an entire block or array rather than an individual cell, these two functions require that you first highlight the entire block to which the instruction applies, and then enter the instruction while simultaneously depressing Ctrl, Shift, and Enter. 

---