Excel Solver may be used to solve simultaneous linear or nonlinear equations. The equa-tions are first written in the form:
FIGURE 5.8 7326_C005.fm Page 103 Tuesday, March 7, 2006 6:19 AM
f1(x1, x2, …, xn) = 0 f2(x1, x2, …, xn) = 0
fn = 0 (5.5)
In the equation set (Equation 5.5) there will be n equations in n unknowns. As noted, the equations may be linear or nonlinear. A new function g(x1, x2, …, xn) is formed such that g = f12 + f22 + … + fn2 (5.6) The solution technique is to allow Excel Solver to iterate on values of x1, x2, etc., to cause the function g to approach zero. Because of the squares in the f functions, they too will approach zero and result in a solution for the set of equations. An alternate formulation would be to express g as a sum of the absolute values of the f function through g =
∑ABS(fi(xi)). For nonlinear equations, multiple sets of solutions may result (including complex solutions), hence restrictions must be placed on the iterative process to match the physical problem represented by the equations. For example, a solution to a heat-transfer problem involving absolute temperatures would require that all the temperatures be positive. These restrictions must be inserted when formulating the problem.
The following is a suggested procedure for setting up the Excel worksheet to accomplish the solution:
1. In column A, type x1=, x2=, etc., for n rows.
2. In column B, adjacent to column A of step 1, insert initial estimates for values of x1, x2, etc., for n rows. These guesses should be made in accordance with the best estimate of the solution for the physical problem represented by the equations.
3. Skip a few rows, then in column A type f1=, f2=, etc., for n rows, where the fs are in the form of a set of equations (Equation 5.5).
4. In column B, adjacent to column A of step 3, type functions (=… ) according to the equation set (Equation 5.5). Use cell locations from step 2 for designating the variables.
5. Skip a few rows.
6. In column A type g=.
7. In column B and the same row as step 6, type (=… ) function according to Equation 5.6. Use cell designations for the functions from step 4.
8. Click TOOLS/SOLVER. Note that if Solver is not present, it must be installed as an add-in. The target cell is that of the function in step 7. Set this target cell equal to zero by changing cells in column B of step 2. Constraints are set according to physical problems. As noted, if absolute temperatures were the variables in a thermal problem, ≥0 might be set as the constraint.
9. A description of TOOLS/SOLVER/OPTIONS is given in Excel Help/Index under Solver, “dialog of box options”. Select options as appropriate.
10. Click Solve. A solution may or may not be obtained. If not, try repeating Solve again. Often, this will produce a solution. Or, alternate initial estimates for the variables in step 2 may be selected and the Solve procedure repeated. A solution
Solution of Equations 105
usually results. If too high a “precision” is specified, Solver may state that a solution is not found when, in fact, one has been found but not to the precision selected. The person formulating the physical problem must make a judgment call in such cases. One should not ask for unreasonable limits of precision when the uncertainties of the physical problem do not justify them.
Example 5.3: Solution of a Set of Algebraic Equations
The worksheet and Solver Windows are shown in Figure 5.9 and Figure 5.10 for solutions of the following set of nonlinear equations:
x1 + 3x2 – 6x32 = −47
The resulting values of the f and g functions are also given. For this problem, the constraint of B3:B5≥0 (positive values) was selected. The exact solutions are x1 = 1, x2 = 2, and x3 = 3. The first set of solutions was obtained for a selected precision of 0.01, whereas the second was for a precision of 0.0001. Note the difference in solutions and the values of the f and g functions.
Example 5.4: Radiation and Convection Heat Transfer between Two Plates A schematic of the system is shown in Figure 5.11. The temperature on the outside of the left plate is Ta, and the temperature on the outside of the right plate is Tb. The plates have different thickness and conductivities. Heat is conducted through each plate and dissi-pated to the fluid. A fluid moves through the space between the plates at temperature Tf, and the inner surfaces of the plates exchange heat with each other by radiation energy, which is proportional to the absolute temperature to the fourth power. In addition, the
FIGURE 5.10
Solution of Equations 107
plates lose heat by convection to the fluid. The convection coefficients are proportional to temperature difference to the 0.25 power.
The energy balance on each inside surface is given in Equation (a) and Equation (b) along with the temperature dependence of Eb1, Eb2, h1, and h2 in Equation (b) through Equation (f). Our objective is to determine the temperatures of the inside surfaces of the walls, T1 and T2.
1000(Ta − T1) + h1(Tf − T1) + (Eb2 − Eb1)/2.25 = 0 (a) 5000(Tb − T2) + h2(Tf − T2) + (Eb1 − Eb2)/2.25 = 0 (b)
Eb1 = 5.67E-8 × T14 (c)
Eb2 = 5.67E-8 × T24 (d)
h1 = 1.6 × (ABS(T1 − Tf))0.25 (e) h2 = 1.6 × (ABS(T2 − Tf))0.25 (f) The purpose of this example is to illustrate the solution of nonlinear equations, so we ask the reader to accept the format of the equations as given. Detailed information on heat-transfer formulations is available in Reference 4.
The Excel worksheet is set up as shown in Figure 5.12, with the six variables located at B4:B9. The outside wall temperatures and fluid temperature are entered in column E with the values that are assigned for this particular case. Other values may be selected if the effects of different boundary conditions are to be examined. Equation (a) and Equation (b) are already in the correct format [ƒ(…) = 0], so they are entered in the worksheet at cells B11 and B12. The g function
g = f12 + f22
FIGURE 5.11
Ta Tb
T1 T2
Flow, Tf
Heat
is entered in cell B14. This is the target cell that we want to iterate to zero by changing values of T1 and T2 in cells B4 and B5.
Examining Equation (a) through Equation (f), we see that the formulas in B6:B9 could have been incorporated in the formulas for f1 and f2. We choose to list them separately so that the calculated values of these quantities will become part of the solution presen-tation. The boundary temperatures in E4:E6 could also be inserted in the formulas, but by using this type of display they also become part of the solution presentation.
(a)
h1= =1.6*(ABS(B4-E6))^0.25 Tf= 300
h2= =1.6*(ABS(B5-E6))^0.25
T1= 761.6657 Ta= 773
T2= 387.9586222 Tb= 373 h1= 7.416549249 Tf= 300 h2= 4.899926743
Solution of Equations 109
The formula displays are removed from the screen and the Solver window called by TOOLS/SOLVER. This window is shown in Figure 5.12c. From the physical nature of the problem it can be inferred that the minimum temperature in the two walls must always be greater than the fluid temperature Tf = 300 K; hence, the constraints are as shown in the Solver window. The initial guesses of 301 and 302 for T1 and T2 are shown at B4 and B5 of the formula window in Figure 5.12a. All temperatures are expressed in absolute (degrees kelvin) because of the radiation terms.
After setting the target cell as B14 = 0, Solve is clicked, and the results are shown in Figure 5.12b. Note the small value of g, i.e., 0.001467 ≈ 0.
Example 5.5: Solution of Simultaneous Linear Equations Using Solver The procedure for solving a set of linear equations with Solver is the same as that for nonlinear equations. First, the equations must be written in the form ƒ(x1, …, xn) = 0.
For this example, we choose the same set of equations that was solved by iteration in Example 5.1.
The listing of the T variables from B1 through B9 is shown in the worksheet in Figure 5.13a. The equations for the ƒ functions are listed from B12 through B20 in this worksheet.
Finally, the g function is listed as the sum of the squares of the ƒ functions in cell B22. The initial guesses for the T variables are all taken as zero in cells B1 through B9.
Solver is then called by clicking TOOLS/SOLVER, and the Solver Parameters window appears as in Figure 5.13c. The target cell that contains the g function is B22, which is set to zero. The cells to be changed for the iteration process are those of the variables in B1:B9.
The constraint that the solutions be greater than or equal to zero is added. Solve is then clicked and a solution is found that appears as in Figure 5.13b. All the ƒ functions and the g function have small values. The solutions for the T variables agree with the values obtained previously.
Although the use of Solver to obtain a solution to simultaneous linear equations is quite satisfactory, it is more cumbersome to use than the iterative technique of Section 5.4.
(c)
FIGURE 5.12 (continued)