Page 340 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 14 NONLINEAR REGRESSION USING THE SOLVER 3 17

partial derivative dF/dai according to equation 14-4 and then restores the
changing cell to its original value and perturbs the next changing cell. The same
method was used earlier in this book to calculate the first derivative of a function
(see "Derivative of a Worksheet Formula Using the Finite-Difference Method" in
Chapter 6).

8F AF F(ai + Aai) - F(ai)
=--
- - (1 4-4)
dai Aai Aa,
The Solver uses a matrix of the partial derivatives to determine the gradient
of the response surface, and thus how to change the values of the changing cells
in order to approach the desired solution.
The use of finite differences to obtain the partial derivatives means that the
Excel spreadsheet performs all of the intermediate calculations leading to the
evaluation of the derivatives. Thus all of Excel's built-in worksheet functions, as
well as any user-defined functions, are supported. The alternative, obtaining the
derivatives analytically by symbolic differentiation of the spreadsheet formulas,
would have been an impossible task.

Loading the Solver Add-In
The Solver is an Excel Add-in, a software program that is loaded only when
needed. You'll find the Solver in the Tools menu; if it's not there, choose Add-
Ins ... from the Tools menu to display the Add-Ins dialog box, shown in Figure
14-4, check the box for Solver Add-In, then press OK.

Why Use the Solver for Nonlinear Regression?
A number of commercial statistical packages provide the capability to
perform nonlinear least-squares curve fitting, so why use the Solver?
First, the Solver is used within the familiar Excel environment, so that you
don't have to learn new commands and procedures.
Secondly, with commercial statistical packages you are generally restricted
to using an equation chosen from a library of fitting functions provided within
the program, whereas with the Solver you can fit data to any model (that is, any
ycalc formula) you choose.
Finally, the Solver is part of Excel. It's free, so why not use it?