Excel matrix operations

Solving Simultaneous Linear Equations with Excel Matrix Operations ... 

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. 

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

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

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. 

Initially, we develop Matlab code and Excel spreadsheets for relatively simple systems that have explicit analytical solutions. The main thrust of this chapter is the development of a toolbox of methods for modelling equilibrium and kinetic systems of any complexity. The computations are all iterative processes where, starting from initial guesses, the algorithms converge toward the correct solutions. Computations of this nature are beyond the limits of straightforward Excel calculations. Matlab, on the other hand, is ideally suited for these tasks, as most of them can be formulated as matrix operations. Many readers will be surprised at the simplicity and compactness of well-written Matlab functions that resolve equilibrium systems of any complexity. 

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

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

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

Somewhat inconsistently, a third matrix operation, TRANSPOSE, is a standard Excel operation. Transposition of a matrix is simply the exchange of rows and columns, i.e., when A has the coefficients ay, then its transpose At has the coefficients ay To transpose a matrix you highlight it, copy it to... 

Usii Example 14.8, we will show how to use Excel to peifbnn certain matrix operations. 

Tfou should know how to perform matrix operations with Excel. 

Sol-gel materials are also known as an excellent matrix for embedding other species due to their tunable physical properties (e.g., flexibility and transparency), high chemical stability, and mild operating conditions. Especially, electrochemical deposition of silane-based sol-gel Aims is usually carried out under mild acidic aqueous solutions at pH 3-6. This allows the co-electrodeposition of silane with nanoparticles [47-50], carbon nanotubes [51-53], metals [54-57], polymers [50,58], enzymes [52,53,59-65], bacteria [66,67], and more. Thus, most of the recent research worlcs also focus on the electrochemical deposition of sol-gel-based composite Aims, with the concern of improving the films performance in corrosion protection, electroanalysis, microextraction, and so on and further broadening the films applications. 

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

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

---