Page 339 - Excel for Scientists and Engineers: Numerical Methods

A more efficient process, the method of steepest descent, starts with a single
set of initial estimate values (a point on the error surface), determines the
direction of downward curvature of the surface, and progresses down the surface
in that direction until the minimum is reached (a modern implementation of this
method is called the Marquardt-Levenberg algorithm). Fortunately, Excel
provides a tool, the Solver, that can be used to perform this kind of minimization
and thus makes nonlinear least-squares curve fitting a simple task.

Introducing the Solver

Like Goal Seek, the Solver can vary a changing cell to make a target cell
have a certain value. But unlike Goal Seek, which can vary only a single
changing cell, the Solver can vary the values of a number of changing cells.
The Solver is a general-purpose optimization package that can find a
maximum, minimum or specified value of the target cell. The Solver code is a
product of Frontline Systems Inc. (P.O. Box 4288, Incline Village, NV 89450;
www. frontsys .corn).
Microsoft's documentation makes no mention of the use of the Solver to
perform least-squares curve fitting, but it is immediately obvious to almost any
scientist that the Solver can be used to minimize the sum of squares of residuals
(differences between Yobsd and ycalc) and thus perform least-squares curve fitting.
The Solver can be used to perform either linear or nonlinear least-squares curve
fitting.

How the Solver Works

The Solver uses the Generalized Reduced Gradient (GRG2) nonlinear
optimization code developed by Leon Lasdon, University of Texas at Austin, and
Allan Waren, Cleveland State University*.
For each of the changing cells, the Solver evaluates the partial derivative of
the objective function F (the target cell) with respect to the changing cell ai, by
means of the finite-difference method. The procedure works something like this:
the Solver reads the value of each changing cell a, in turn, modifies the value by
a perturbation factor (the perturbation factor is approximately 1 0-8), and writes
the new value back to the worksheet cell. This causes the spreadsheet to
recalculate, producing a new value of the objective. The Solver calculates the

* For linear and integer problems, the Solver uses the simplex method and branch-and-
bound method, but these methods need not be discussed here. You can read more about
the design and operation of the Solver in the following article (available online): "Design
and Use of the Microsoft Excel Solver," Daniel Fylstra, Leon Lasdon, John Watson and
Allan Waren, Interfaces 28, September 1998, pp. 29-55.