Excel MINVERSE function

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. 

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. 

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... 

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. 

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. 

While the MINVERSE and MMULT functions are sufficient to solve well-posed linear systems, there are many more functions that can lead to more robust computations. A completely free Excel Add-On called Matrix.xla can be downloaded from the website http //digiIander.libero.it/foxes/SoftwareDownload.htm. In addition to the software, there is a comprehensive manual (in two volumes). Readers are encouraged to install this software on their own computer. Once Matrix.xla is installed, aU Excel applications have an enhanced set of functions available for matrix operations... 

---