Matrix operations in Excel

The few short programs presented in this chapter may also serve as a very rudimentary introduction into Matlab. Readers not familiar with Matlab but otherwise proficient in an alternative language will be surprised at the almost complete lack of for. .. end loops. We also introduce matrix operations in Excel, assuming that the other, more common aspects of Excel are known to the reader. While there is a reasonable collection of matrix operations available in Excel, their usage is rather cumbersome. We believe that many readers will appreciate the short introduction into this aspect of Excel. 

In an Excel spreadsheet, the [TRANSPOSE function can be applied to an array of data. For this, we need to become familiar with the two most important rules to perform matrix operations in Excel ... 

In contrast to Matlab, where the defaults are the matrix operators, in Excel the default is the element-wise operation. In fact, all basic operations (e-g- 0, 0> 0> 0> Q) and functions (e.g. EXP, LN, LOG) work element-wise in Excel. All... 

The MLR dialog box, illustrated in Figure A.32, is somewhat simpler than the others and is mainly used if two out of X, C and S are known. The type of unknown matrix is chosen and then regions of the spreadsheet of the correct size must be selected. For small datasets MLR can be performed using standard matrix operations in Excel as described in Section A.4.2.2, but for larger matrices it is necessary to have a separate... 

In Excel, the cells comprising the prospective result Y have to be pre-selected as we have already seen for matrix transposition. For this, we need to predict the dimensions of Y from the row dimension of C and column dimension of A. Also, there is no direct operator for matrix multiplication in Excel. The function MMUUI in conjunction with the SHIFT+CTRL+ENTER key... 

Finally, many chemometricians use matrices. Matlab (see Section A.5) is better than Excel for developing sophisticated matrix based algorithms, but for simple applications the matrix operations of Excel can also be translated into VBA. As discussed in Section A.4.4.2, this is somewhat awkward, but for the specialist programmer there is a simple trick, which is to break down the matrix expression into a character string. Strings can be concatenated using die sign. Therefore, the expression... 

Matlab is a matrix oriented language that is just about perfect for most data analysis tasks. Those readers who already know Matlab will agree with that statement. Those who have not used Matlab so far, will be amazed by the ease with which rather sophisticated programs can be developed. This strength of Matlab is a weak point in Excel. While Excel does include matrix operations, they are clumsy and probably for this reason, not well known and used. An additional shortcoming of Excel is the lack of functions for Factor Analysis or the Singular Value Decomposition. Nevertheless, Excel is very powerful and allows the analysis of fairly complex data. 

In Excel, mathematical operations of one or more cells can be dragged to other cells. Since a cell represents one element of an array or matrix, the effect will be an element-wise matrix calculation. Thus, addition and subtraction of matrices are straightforward. An example ... 

The capability of dragging results from one cell to others is a very useful property of Excel and becomes even more powerful in combination with the dollar operator ( ) correctly applied within the cell reference. Referring to the previous Matlab example, if the scalar element foi,2 (cell F3) is to be subtracted from matrix A (A3 C4) in Excel, putting the dollar operator ( ) in front of the column and row reference of the source cell containing the scalar foi,2 ( F 3), prevents "dragging-over" of the source cell F3 in both column and row direction. 

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. 

The absorbance of a mixture is the sum of absorbances of the individual components. At a minimum, you should be able to find the concentrations of two species in a mixture by writing and solving two simultaneous equations for absorbance at two wavelengths. This procedure is most accurate if the two absorption spectra have regions where they do not overlap very much. With a spreadsheet, you should be able to use matrix operations to solve n simultaneous Beer s law equations for n components in a solution, with measurements at n wavelengths. You should be able to use Excel SOLVER to decompose a spectrum into a sum of spectra of the components by minimizing the function (Aca c — Am)2. 

It is a very useful facility to be able to combine matrix operations. This means that more complex expressions can be performed. A common example is to calculate the pseudoinverse (Y. Y ) l. Y which, in Excel, is... 

In this chapter we will encounter a number of standard mathematical operations that are conveniently performed and/or illustrated on a spreadsheet. We start with a brief description of the logic underlying the Goal Seek and Solver methods of Excel. Then we consider two methods often encountered in spectroscopy, viz. signal averaging and lock-in amplification. Subsequently the focus shifts toward numerical methods, such as peak fitting, integration, differentiation, and interpolation, some of which we have already encountered in one form or another in the context of least squares analysis and/or Fourier transformation. Finally we describe some matrix operations that are easy to perform with Excel. 

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

---