Page 462 - Excel for Scientists and Engineers: Numerical Methods

APPENDIX 8 ANSWERS AND COMMENTS FOR PROBLEMS 439

cubic (polynomials of 2nd or 3rd order) fitted the data quite well. Using that
information I used LINEST to find regression coefficients that fitted Power to
Speed (5') for each value of Throttle (0 (the fitting function was a,!? + b.5').
I then fitted the regression coefficients a and b individually vs. Throttle.
(From charts, it appeared that a could be fitted using a 3-term function, b
using a 2-term function.) The final fitting function was (c*p + d.T + e)p +
(fT + g)S. The g term had a large standard error and perhaps could be
eliminated or modified.
The final sheet in the workbook shows how the Solver (see following
chapter) can be applied to the same data. Both the preceding 5-term fitting
function and a 6-term fitting function, (cap + d.T + e)9 + VT + g)S + h,
were tried.
The preceding fitting function can be written in the following form:
c.p-9 + d.Fp + e-9 +fFS + gS + h

Chapter 14 Nonlinear Regression and the Solver

1. Enter formula for Acdc (you'll need a cell for k, the changing cell). Enter
formula for (residual)* and sum the squares of residuals (this is the target
cell). Use the Solver to minimize the target. Answer: k = 0.3290.

2. Follow the same procedure as in problem 1. Answer: a =.0.5005.

3. Follow the same procedure as in problem 1, except that there are four
changing cells. Answer: a = 1.0644246, b = 1.8495246, c = -0.8966248, d =
9.97124864.

4. The answer spreadsheet has been set up with headings for using the Runge3
custom function. The workbook contains a "Data for Problem" sheet and the
complete problem.

5. Follow the same procedure as in problem 1, except that there are three
changing cells. Answers I got were A = 0.10119, B = 5.1337, C =
0.01 17922.

6. This example requires scaling. The data for the exercise and the answer
spreadsheet are in different workbooks.

8. The workbook contains a worksheet with the raw data, plus two worksheets
with solutions. You can compare the use of wavelength vs. wavenumber as
the independent variable in deconvoluting W-visible spectra. Although it is
generally considered that an independent variable that is proportional to
energy (e.g., wavenumber) is the correct independent variable to use, in this