The Pseudo-Inverse in Excel

We have encountered Excel s LINEST as a tool for linear regression. Unfortunately, LINEST cannot be generalised from vectors to matrices. To deal with matrices, we do not have an option but to use equations (4.59) and (4.61). It is possible to do so, but not as convenient as in Matlab. 

In order to keep the spreadsheet reasonably small, the dimensions are much smaller than those in the Matlab examples. It is still a consecutive reaction scheme the spectra were recorded at 11 times and at 6 wavelengths. 

There are two paths to reach the result. The construction of a series of intermediate matrices, 

This approach uses a lot of space on the spreadsheet, in particular the transpose of a long column is a veiy wide row. However, it is reasonably easy to detect potential errors in the formulas. 

The other path does the whole calculation in one step, the result is the shaded matrix Aone eq- This is a veiy neat approach on the spreadsheet, but a very difficult equation to be entered in the matrix mode  

---

The spreadsheet in Figure 4-62 is heavily matrix based (see Chapter 2, for an introduction to basic matrix functions in Excel). It is the only way to keep the structure reasonably simple. The matrix C in cells A21 C31 is computed in the usual way, see equation (4.63) the parameters required to compute the concentration matrix are in cells Q4 S4, they include the initial concentration for species A and the two rate constants that are to be fitted. In cells E 16 018 the computation of the best absorptivity matrix A for any given concentration matrix C, is done as a matrix equation, as demonstrated in The Pseudo-Inverse in Excel (p.146). Similarly the matrix Ycaic in cells E21 031 is written as the matrix product CA. Even the calculation of the square sum of the residuals in cell R7 is written in a compact way, using the Excel function SUMXMY2, especially designed for this purpose. We refer to... 

---