8 Deposit numerical values in block I3:K7. You may want to start with those listed in the
legend of Fig. 3.3-2, and then (after you have made the on-the-sheet graphs) vary those
parameters to your taste.
9 Make on-the-sheet graphs of C3:C1002 and D3:D1002 vs. A3:A1002.
10 We start with the simplest possible ﬁlter, one that merely averages a number of adja-
cent data to reduce the noise. Go to cell E15, and there deposit the 25-point averaging
formula=(D3+D4+D5 +…+D27)/25. Copy this instruction down to E991.
11 Add a graph of the so ﬁltered spectrum. For reference you may want to include in that
plot either the noise-free curve (as in Fig. 3.3-2) or the noisy one.
12 For the second ﬁlter, in cell F3, we use instead the formula=(-253*D3-138*D4-
-253*D27)/5175, where we use the convoluting integers for a
cubic 25-point smoothing ﬁlter: -253, -138, -33, 62, 147, 222, 287, 342, 387, 422, 447,
462, 467, 462, 447, 422, 387, 342, 287, 222, 147, 62, -33, -138, and -253. Note that
these numbers are symmetrical around the middle of the set. The corresponding nor-
malizing factor is 5175. Again, copy this formula to F991, and plot the resulting curve.
13 Finally, in column G, calculate a third ﬁltered curve, this time using the convoluting
integers for a ﬁfth-order 25-point smoothing ﬁlter: 1265, -345, -1122, -1255, -915,
-255, 590, 1503, 2385, 3155, 3750, 4125, 4253, 4125, 3750, … , -345, 1265. The normal-
izing factor is 30 015. Again plot the data.
14 Save as NoisySpectrum.
Figure 3.3-2 shows typical results. The simple ﬁrst-order averaging ﬁlter,
panel (c) in Fig. 3.3-2, is most eﬀective in reducing the noise, but also intro-
duces the largest distortion, visible even on the broadest peaks. This is
always a trade-oﬀ: noise reduction is gained at the cost of distortion. The
same can be seen especially with the narrower peaks, where the higher-
order ﬁlters distort less, but also ﬁlter out less noise. In section 10.9 we will
describe a more sophisticated ﬁlter, due to Barak, which for each point
determines the optimal polynomial order to be used, and thereby achieves a
better compromise between noise reduction and distortion.
Weighted least squares
The least-squares analysis we have encountered so far works with a special
class of functions, namely polynomials. (In chapter 2 we considered the sim-
plest polynomials, the functions y=a
, y =a
x, and y =a
x; in sections
3.1 and 3.2 we used y =a
and y =a
Many types of experimental data can be described in terms of polynomials.
3.4 Weighted least squares
However, there are also many types of data that do not readily ﬁt this mold. A
prime example is a single exponential: we saw in the preceding section that
ﬁtting an exponential to a quadratic is not very satisfactory. Moreover,
invoking higher-order terms does not really help.
Consider the concentration of a chemical species reacting according to a
ﬁrst-order irreversible reaction A →B with reaction rate constant k. In this
case the concentration of A is described by [A]=[A]
exp(-kt) where [A] is
the concentration of species A, t is the time elapsed since the beginning of
the experiment (at t=0), and [A]
is the corresponding initial concentration,
. Imagine that we follow the concentration of A spectrometri-
cally, and want to extract from the resulting data the initial concentration
and/or the rate constant k. In that case we must ﬁt the exponential to a
polynomial. Using a least-squares polynomial ﬁt, this is no simple task.
On the other hand, it is easy to transform the exponential into a polyno-
mial, by simply taking the (natural) logarithm. This yields the equation of a
straight line, ln[A]=ln[A]
-kt, which is of the form y =a
xwith y =
=-k, and x =t, the ‘independent’ variable. There is only
one minor problem: in analyzing ln[A]=ln[A]
-kt instead of [A]=[A]
exp(-kt) we minimize the sum of squares of the deviations in ln[A] rather
than those in [A]. When the dominant ‘noise’ in [A] is proportional to [A],
minimizing the sum of the squares of the deviations in ln[A] would indeed
be appropriate. However, it will not be so when the magnitude of that noise
is independent of [A]. In the latter case we can still use the transformation as
long as we include with each input value y
a weight w
to transform the noise
The weights depend on the particular transformation used. In general,
when the experimental data y are transformed to some new function Y (as in
the above example, where Y=ln y), the corresponding global weight w will
be given by
Weighted least-squares analysis is also called for when we must average
data of diﬀerent precision. In section 2.5 we already encountered the need
for weighting of the experimental data when their individual standard devi-
ations are known. In that case the individual weights are simply the recipro-
cals of the variances of the individual measurements,
In general, then, there are two quite diﬀerent reasons for using weighted
least squares: (a) global weights may be required when the data analysis
involves a transformation of the dependent variable, while (b) individual
weights are needed when we consider data of diﬀerent (but known)
More on least squares
precision. The variances of individual data are seldom known, which is why
the use of global weights is the more common. However, when both (a) and
(b) apply simultaneously, we have the general expression for the total
of which (3.4-1) and (3.4-2) are the special cases for equal variances
constancy of dY/dy respectively.
We saw in sections 3.1 and 3.2 that the mathematical expressions to ﬁt
data to a multi-parameter function, or to a higher-order polynomial such as
a quadratic, can be quite daunting, but that they are readily accessible when
the spreadsheet has built-in facilities to do the least-squares analysis. Excel
does not have comparable built-in capabilities for weighted least-squares
analysis. However, it does have the facility to accept add-in programs coded
in a language Excel understands, VBA (=Visual BASIC for Applications),
and you may already have used the added Weighted Least Squares in sec-
tions 3.1 and 3.2. If you are curious how such an add-in program works,
consult chapter 10, where it is described in detail, including its complete
The example we will use below is again that of an exponential decay: y =y
exp(- kt). We already generated such a signal, in NoisyExponential, without
and with added Gaussian noise. For the exponential y=y
exp(–kt) we have
Y=ln y and w =1/(dY/dy)=y
Instructions for exercise 3.4
1 Recall NoisyExponential.
2 Add column headings in row 4 for Y, w, t, yw, and ynw.
3 In cell H6 deposit‘=LN(D6)’, in cell I6‘=D6^2’, and in cell J6‘=A6’, then copy these
three instructions down to row 56. (We here repeat the column for t in order to ﬁt the
ﬁxed format required by the Weighted Least Squares macro.)
4 Highlight these three columns, starting at H6 and extending it down as far as column H
contains numbers. At a given moment, noise will make y negative, at which point Y
cannot be calculated, which will show as #NUM!. That point, and the data following it,
cannot be used, because they are clearly biased towards positive values of y, without
the negative y-values contributing to Y.
5 Call the Weighted Least Squares routine (T
6 Also call the Regression analysis, and apply it to the same data set. (You could also use
the Weighted Least Squares analysis for this, after copying the values in columns H and
3.4 Weighted least squares
J to, say, columns M and O, provided these are not used otherwise, and column N is
empty. If you were to do the unweighted analysis in the block starting at H6, you would
have to erase the data in column I, and you would overwrite the earlier results.)
7 In order to compare the results with the theoretical values in row 1, use the spread-
sheet to take the antilogs ( =10^…) of the coeﬃcient and standard deviation of the
intercept, because both routines yield Y
rather than y
8 In K6:K56 and L6:L56 calculate the curves y
for the weighted and unweighted
curves, each reconstituted with the parameters provided by the weighted or
unweighted least-squares ﬁttings respectively, then plot these together with the data
in B6:B56 and D6:D56. They might look like Fig. 3.4.
Because no two sets of noise are alike, your plot will be diﬀerent, but most
likely the conclusion will be similar: for this type of data, where the noise is
constant, a weighted least-squares ﬁt is far superior to an unweighted ﬁt. The
numerical data tell the same story: in this particular example, the weighted
least-squares ﬁt yielded a slope -k of -0.0991±0.0036, and an intercept ln
=-0.0214 ±0.0224 so that y
=0.952±5.3%, whereas the unweighted ﬁt
gave -k=-0.1181 ±0.0076, ln y
=0.0800 ±0.1363 hence y
However, the opposite conclusion would be drawn if the noise had been pro-
portional to the magnitude of the signal y, and you had not taken that into
account in the weights! You can use the spreadsheet to verify this yourself.
The moral of this exercise is that you need to know something about the func-
tion to which you ﬁt (in order to use the correct transform), and about the
nature of the noise in your data, before you can get rid of most of it. And, as
always, the less noise you have to start with, the better oﬀ you are.
More on least squares
Fig.3.4:Fitting a noisy exponential curve (points) with unweighted (thin black line) or
weighted (solid black line) least-squares ﬁt. The underlying, noise-free curve (heavy
colored line) is shown for comparison.
Another example of weighted least squares: enzyme kinetics
Enzymekineticsprovideaprimeexampleof theapplicabilityof weightedleast
themquantitatively,and(b)thereare severalmethods toanalyzethedata,i.e.,
diﬀerent ways of linearizing the theoretical expression. When unweighted
least-squares analysis is used,these diﬀerent analysis methods yield diﬀerent
results when operating on the very same experimental data!When weighted
The expression for the simplest form of enzyme kinetics was ﬁrst given by
Henri (Compt. Rend. 135 (1902) 916), but is often named after Michaelis &
Menten (Biochem.Z. 49 (1913) 333), who investigated the same enzyme and
used the same equation. The expression is
where v is the initial rate of the enzyme-catalyzed reaction, S is the concen-
tration of its substrate, v
is the maximum rate, and Kis a constant.
One popular way to rectify (3.5-1) is to convert it to the Lineweaver–Burk
so that a plot of 1/v versus 1/S yields a straight line with intercept 1/v
. Another linearization, due to Hanes, is obtained by multiplying
all terms with S:
where a plot of S/v versus S yields a straight line of intercept K/v
. Typically, the determination of the initial rate v is the dominant source
of experimental uncertainty, and below we will use that as our starting
assumption. We will use a data set from M. R. Atkinson, J. F. Jackson, & R. K.
Morton, Biochem. J. 80 (1961) 318, to illustrate the need to use weighted
least-squares analysis in such a case. The experimental data are as follows:
Initial rate vof nicotinamide-
adenine dinucleotide formed,
nucleotide, in mM
3.5 Another example of weighted least squares: enzyme kinetics
The weighting factors needed for the above two cases are diﬀerent. For the
Lineweaver–Burk plot we have y= v, Y=1/v, and therefore, according to
the Hanes plot we ﬁnd Y=S/v so that w=v
Instructions for exercise 3.5
1 Open an Excel spreadsheet.
2 Enter the column headings S, v, 1/v, w, 1/S, S/v, w, and S.
3 Enter the data in the ﬁrst two columns, labeled S and v.
4 In the next columns, calculate 1/v, w=v4, 1/S, S/v, w=v4/S2, and copy Sfrom the ﬁrst
5 Highlight the data in the third through ﬁfth column, and click on T
acros WLS1. The coeﬃcients will appear below the data.
6 Similarly, highlight the data in the last three columns, and analyze them the same way.
7 The Lineweaver–Burk analysis yields slope K/v
and intercept 1/v
. Use the next two
rows to convert this information into the parameters of interest, Kand v
. Here, K=
slope / intercept, and v
=1 / intercept.
8 Calculate the corresponding standard deviations, either by hand (using the rules of
error propagation through quotients, i.e., via relative errors) or (easier and less error-
prone) with the macro Progression. Note that Progression handles only one parameter
at a time, so that you must apply it separately to Kand v
. Also note that the weighted
least-squares macro places the standard deviations underthe coeﬃcients, whereas
Progression puts them next to the parameters.
9 Similarly, the Hanes analysis yields slope 1/v
and intercept K/v
, from which you
again compute Kand v
, and the corresponding standard deviations. Note that the
roles of slope and intercept are interchanged in the Lineweaver–Burk and Hanes plots.
Compare the results of the two approaches.
10 Copy the entire block down.
11 Modify the just-copied block by replacing all weights by 1’s.
12 Now repeat the analysis. Forcing all terms w to 1 will of course yield the equivalent,
13 Compare what you got; it should look like Fig. 3.5.
The unweighted Lineweaver–Burk and Hanes plots yield diﬀerent answers
even though they analyze the same data set. On the other hand, when we use
appropriate global weighting factors, both methods yield identical results,
as they should. Need we say more about the importance of proper weight-
More on least squares
Non-linear data ﬁtting
So far we have seen that experimental data can be ﬁtted to many functions,
such as a line, a polynomial, or (after transformation) an exponential.
However, there are many more functions for which this does not seem pos-
sible. For example, no way is known to ﬁt y =a
using what is called a linear least-squares ﬁt, where the term linear refers to
the fact that the expression is linear in the coeﬃcients, here a
(As we have seen, the expressions for y can be quite non-linear in the inde-
pendent parameter x; that is still considered a linear least-squares ﬁt.)
3.6 Non-linear data fitting
Fig.3.5:Spreadsheet for the analysis of the data on the kinetics of nicotinamide
mononucleotide adenyltransferase by Atkinson et al., Biochem.J. 80 (1961) 318. The
results should be read as, e.g., on line 30: K=0.4406 ±0.0906 for the unweighted
Lineweaver–Burk method, K=0.5821 ±0.0522 for the unweighted Hanes plot, or, on
line 31, v
=0.5853± 0.1039 for Lineweaver–Burk, v
=0.6848 ±0.0383 for Hanes.
In order to overcome this limitation of linear least-squares ﬁtting, i.e., its
restriction to a speciﬁc type of function, ingenious algorithms have been
developed to ﬁt data to any function that can be described analytically,
using a single criterion, such as minimizing the sum of squares of the devia-
tions between the data and the model. Such routines use a variety of tech-
niques, often including a method of steepest descent as well as a
Newton–Raphson algorithm, to as it were ‘feel’ their way towards that criter-
ion. Excel has several such algorithms in its Solver. Below we will use several
examples to demonstrate both the power, and some of the limitations, of
Some kinetic data
First we will get acquainted with the method by applying it to a set of experi-
mental multi-parameter data from a paper on the kinetics of the thermal
isomerization of bicyclo[2.1.1]hexane by R. Srinivasan & A. A. Levi, J. Am.
Chem. Soc. 85 (1963) 3363, as quoted in N. R. Draper & H. Smith, Applied
Regression Analysis, 2nd ed., Wiley 1981). In order to reduce the tedium of
having to enter 38 data sets, we have used a smaller subset for this example,
leaving out all duplicate measurements as well as all data at 612 K and 631 K.
The dependent variable y is the fraction of the parent compound remaining
after a reaction time of t minutes, while T is the temperature of the experi-
ment, in K. The data are as follows:
Reaction time t, in min
Temperature T, in K
0.877 0.827 0.787
You will notice that the temperature range investigated is rather limited,
and that not all possible time–temperature combinations were measured.
Still, the fractions range from 0.3 to 0.95, and there are enough data to work
Instructions for exercise 3.6-1
1 Open an Excel spreadsheet.
2 In cells A1 and A2 enter the labels a and b respectively.
3 In cells B1 and B2 deposit initial guess values; 0 and 0 will do for now.
4 In cell D1 deposit the label SRR.
5 In cells A4 through E4 place the labels time t, temp T, y(exp), y(calc), and RR.
More on least squares
Documents you may be interested
Documents you may be interested