8 - Standard mathematical operations

Summary

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.

The Newton–Raphson method

The Newton–Raphson method is often used to solve problems involving a single variable, and is implemented in Excel as Tools ⇨ Goal Seek. The method requires that a function F(x) can be formulated as an explicit mathematical expression in terms of a variable x. We now want to know for what value of x the function F(x) has a particular value, A. The Newton–Raphson approach then searches for a value of x for which F(x) is equal to A. Often one selects A = 0, in which case the corresponding value of x is called a root of the function F(x).