Iterative formula spreadsheet

Exercise 1.3 Set up a spreadsheet that implements the secant method and then solve each of the problems from Exercise 1.1. Use the graph of each function to select an initial guess. Recall the iteration formula for the secant method ... 

As an example of a weak acid problem that can readily be solved by circular reference and iteration, consider the calculation of the pH of a 0.1000 M solution of sodium bisulfate, NaHS04. The bisulfate ion is a relatively strong weak acid with Ka = 1.2 X 10 . (Roughly speaking, weak acids with values greater than 10 will require the quadratic or successive approximations approach.) The spreadsheet fragment in Figure 10-13 illustrates a case of a cell that contains a circular reference. Cell C5 contains the formula =SQRT( C 3 (A5-C5)). 

Copy the value of a variable or a formula in one cell into other cells Execute an iterative calculation using the spreadsheet function... 

You are now ready to enter the equations for the internal and boundary nodes. Some spreadsheets may start solving the equations as you enter them leading to all sorts of error messages. To avoid this outcome, define a constant in, say, cell A3 (first column, third row) to be zero, and multiply each equation by A3 as you enter it. Then, when you have completed entering all the equations, you need to solve the problem, change A3 from 0 to 1, and begin iteration. A formula is entered in a cell, but the formulas themselves are not displayed in the cell on the screen. What is displayed is the value given by the formula. For example, to show the product in cell DIO of the feed located in cell F24 and the concentration located in F25, you would enter into cell DIO the formula F24 F25. It is recommended that you set up the overall material balances about specific process units in addition to the equations entered on the main part of the spreadsheet. If you take this step, you can quickly check for errors in the setup of the balances. Also, as you enter equations and data, check interim calculations just as you would check out a computer code as it was written. 

Microsoft Excel has a very useful program called Solver. To use this program, we must first arrange an equation to give a formula equal to a known value. Solver effectively calculates the formula in reverse, by starting with the answer of the formula (the known value) and, by iteration, solving for the value or values of one or more variables in the formula that results in the correct answer. (Other spreadsheet programs have similar problem solvers.)... 

Alternatively, most queueing models can be easily incorporated into spreadsheets because they are either formulas or fairly straightforward iterative calculations. 

Given the temperature at the core centre and the decay power, not including the dimensions of the various parts, the concrete temperature necessary to dissipate the heat produced has to be calculated. The problem, once the input data are added to the spreadsheet, is easily solved by subsequent iterations given the rapidity of the calculation. The formulae for calculating the decay heat are also given as a function of the time elapsed since the shutdown and the operating power. 

Lap-shear stress predictions are successfully achieved with Excel spreadsheets due to the continual iterations of parameters and formulas for each individual CFRP and adherend material, geometrical variations, and environmental preconditioning temperature usually involved in large-scale investigations. Figure 10.13(a) and (b) displays the lap-shear stress predictions... 

---