Page 463 - Excel for Scientists and Engineers: Numerical Methods

440 EXCEL: NUMERICAL METHODS

example a better fit is found when using wavelength as the independent
variable.
On the sheet "Deconvolution using wavenumber," wavelength (nm, 1 x
m) is converted into wavenumber (cm-') by using the relationship
wavenumber = lOOOO/wavelength.

9. The data for the exercise and the answer spreadsheet are in different
workbooks. The spectrum contains a number of bands. I have not yet
obtained a satisfactory solution.

10. Using the spectra of the pure species, calculate the E for each of the three
species, cobalt, nickel and copper, at each wavelength. Then, at each
wavelength, use the relationship

Aobsd = ECocCo + ENicNi + &ucCu
You now have 236 equations with only three unknowns. Use the Solver to
find the three unknowns. The answers are slightly different from the results
found in Chapter 9.

1 1. The equations in the problem lead to the following worksheet formula for the
absorbance:
=TL*(K*eL+H*eHL)/(K+H)
(Names were used for all cell references in this worksheet.) The changing
cells are the log K value and the EL and EHL values, one pair for each column
of absorbance values at a particular wavelength.
Since the data table is large, it was most convenient to have the experimental
absorbance values on one sheet and the calculated values on another.
The SUMSQ worksheet function was used to calculate the sum of squares of
residuals for each column.
I used the Solver on the absorbance values at 260 nm first, to get a value of
logK (changing cells and target cell for this calculation are in red). I then
used these as starting values for the global refinement. Convergence was
very slow.

12. The five changing cells have very different magnitudes (values were
estimated from the data table and/or the chart); three were of magnitude 10"
and two were of magnitude 1. Using the Solver in the usual way did not give
a reasonable solution (see the sheet "First Trial"). Checking the Use
Automatic Scaling box did not give a reasonable solution either (see "With
Automatic Scaling"). Manual scaling was done as described in the
worksheet "Manual-kAutomatic Scaling" and this led to an acceptable
solution.