Tutorial on Using Excel Solver to obtain Empirical Process Models:
1. Empirical process models.
For a linear single input-single output (SISO) process, a general model that is useful for obtaining transfer functions is given by the standard form: in which the output and input variables are in the form of deviation variables.

It can be shown that an equivalent system of equations involving only first order derivatives (more easily amenable to Euler integration) is given by 2. (To help illustrate the procedure, we will use a third order process as an example, We will assume that the data is sampled using a uniform time increment, Dt. Further, we assume that the initial segment of the data are at steady state. A zipped copy of the associated Excel file can be downloaded to help in the discussion ).
1. Set up the cell locations for coefficients, sampling time, and input/output data.

2. If the input/output data are not yet in the form of deviation variables, calculate the deviation input/output variables.

If the system is stable, put down additional constraints on the coefficients of the characteristic polynomial equation: , such that the roots will always have negative real parts.

For example, if the polynomial is second order, we need: . For a third order system, we need: . (These constraints plus other rules for higher order polynomials can be obtained from the Routh-Hurwitz arrays.)  Without violating any of the constraints, enter some initial guesses to the model parameters.  (In this example, we begin with a guess of a2=3, etc. ) 3. Prepare the columns for z1, , zn. Initialize the first entries with 0's. Then fill the other entries with the appropriate formula via an Euler approximation of the differential equations described above.

4. 1. Build a column that calculates the square of the error: (ydev-z1)2

2. 3. Allocate a cell for the Root Mean Square (RMS) measure which is literally the square root of the average of the squared errors.

4. 5. Mark the RMS cell. Then select SOLVER item from the TOOLS menu. A "Solver" window should pop up. • Choose the minimize option
• Under the "by changing Cells" box, include the cell entries for A2, A1, A0, B2, B1, B0 entries. (If some of the parameters are known and do not need to be changed, then do not include these cells. In our example, we will fix B2=0).
• Add the constraints needed for a stable model. In our example, these are A2>0, A0>0 and

• (A1-[A0/A2])>0.
• Click the [Solve] button to initiate the optimization.
1. Plot the predicted data, z1, vs time, and compare this with (ydev vs time)-plot to assess how good the estimated model matches the data. This page is maintained by Tomas B. Co (tbco@mtu.edu). Last revised 1/18/2000.

Tomas B. Co
Associate Professor
Department of Chemical Engineering
Michigan Technological University
1400 Townsend Avenue
Houghton, MI 49931-1295