Spreadsheet, nonlinear equation

Graphical representation and corresponding equations of tabular data are of value for interpolation, for revealing behavior patterns, and other purposes. The most complete software for this purpose is TableCurve. More limited but adequate for the present book are POLYMATH and MathCad. All spreadsheet software, for instance QuattroPro, can make plots of all kinds. In this book the most used software for making plots of tabular data and equations is AXUM which is also capable of fitting polynomials and nonlinear equations to tabular data. All of these commercial packages have tutorials and user friendly instructions. 

For a single complex nonlinear equation of the form /(,t) = 0, use a spreadsheet or an equation-solving program. If you use a spreadsheet, put an estimated value of x in one cell and the formula for f(x) in a second cell, then use the goalseek tool to set the value in the second cell equal to zero by varying the value in the first cell. The final value in the first cell is the desired solution. 

Alternatively, all thej model equations may be listed singly, and then solved simultaneously using a standard nonlinear equation solver, such as a spreadsheet program. For the two-component system, the equations include the van Laar equations for both components in each liquid phase ... 

Prepare an Excel spreadsheet that calculates for a mixture the bubble point at pressures of 15,16,..., 25 atm and produces a plot of the bubble point versus pressure with suitable annotations and title. Use Goal Seek to solve the single nonlinear equation at each pressure. Data for the system are given in Figure 1.4. 

There are two ways in which to use Solver for nonlinear equations. The direct way is to set up the nonlinear equations eis constraints with no objective function. The other way is to set up the spreadsheet to compute the sum of squares of residuals and use Solver to minimize this (without any constraints). The latter method is used in the following spreadsheet, where the feed consists only of component A with Qq = 1. The volumetric flow rate is 50 gmol/s, and the reactor volume is 100 L/s. The equations are rearranged in the form f(x) = 0 so that the left-hand sides are residuals whose value at a solution is zero (within tolerance). The initial guess for all concentrations is 0.5 gmol/L. 

This spreadsheet solves the problem of a stagnation flow in a finite gap with the stagnation surface rotating. This problem requires the solution of a nonlinear system of differential equations, including the determination of an eigenvalue. The problem and the difference equations are presented and discussed in Section 6.7. The spreadsheet is illustrated in Fig. D.7, and a cell-by-cell description follows. 

Carry out the least-squares minimization of the quantity in Eq. (7) according to an appropriate algorithm (presumably normal equations if the observational equations are linear in the parameters to be determined otherwise some other such as Marquardf s ). The linear regression and Solver operations in spreadsheets are especially useful (see Chapter HI). Convergence should not be assumed in the nonlinear case until successive cycles produce no significant change in any of the parameters. 

If you have access to a spreadsheet program, finding solutions of nonlinear single-variable equations is relatively easy. If the equation has the form f(x) = 0, you need only enter a guessed value of. v in one cell of the spreadsheet, insert the formula for f x) in an adjacent cell, and then vary the value in the first cell until the value in the second cell is close enough to zero to meet a specified convergence criterion. The next example illustrates this approach. 

Homemade models are often mass and energy balance spreadsheets, simplified kinetic models, or the simultaneous solution of the convection diffusion and heat equations together with nonlinear isotherms. All levels of models have their place. 

Spreadsheet Summary The second exercise in Chapter 13 of Applications of Microsoft Excel in Analytical Chemistry involves enzyme catalysis. A linear transformation is made so that the Michaelis constant, K, and the maximum velocity, can be determined from a least-squares procedure. The nonlinear regression method is used with Excel s Solver to find these parameters by fitting them into the nonlinear Michaelis-Menten equation. 

Most determination methods finally lead to discrete loading versus concentration data that have to be fitted to a continuous isotherm equation. For this purpose it is advised to use a least-squares method to obtain the parameters of the isotherm. Nonlinear optimization algorithms for such problems are implemented in standard spreadsheet programs. To select an isotherm equation and obtain a meaningful fit,... 

---