Spreadsheet Goal Seek

To have consistent units, k has to be in m/s. If Mg -Mg = 0, then the calculation is correct. If not, use a new value of Mg guggg and repeat. This is relatively straightforward on a spreadsheet. Goal Seek or Solver can be used to converge Mg. 

As an alternative to using the eomputer program (PROG 16), Equation 8-162 ean be expressed in a spreadsheet. Using the GOAL-SEEK eommand from the Tools menu (Appendix B) from the Exeel spreadsheet menu to determine the Peelet number gives f(Npg) = 0. This funetion is ineorporated in the spreadsheet. The eomputed Peelet number is Np = 0.8638. 

You can solve for equilibrium concentrations in a chemical reaction by first creating a table of initial and final concentrations of each species. Then find the final concentrations by systemic guessing with a spreadsheet or by using the Excel GOAL SEEK routine. 

The solution is even easier to obtain if the spreadsheet program is equipped with agoalseek tool. Once the first of the spreadsheets shown above has been constructed, select Goal Seek (it can normally be found under the pull-down Tools menu), and enter B2 as the target cell, 0.0 as the target, and A2 as the variable cell. The spreadsheet will then search for and (usually) converge on the solution within a fraction of a second. 

The DCFROR can then be found by adjusting the interest rate until the NPV is equal to zero. This is easily accomplished in the spreadsheet using the Goal Seek tool, giving DCFROR = 40%. 

The DCFROR (IRR) of the project after 15 years of production at full capacity can be found by either adjusting the interest rate (manually or using the goal seek function) until the NPV at the end of year 18 is equal to zero, or by using the IRR function in the spreadsheet over the range year 1 to year 18. The working capital should be included as a recovered cost in year 18. 

Spreadsheet Summary In the fmst exercise in Chapter 6 of Applications of Microsoft Excel in Analytical Chemistry, we explore the use of Excel s Solver to find the concentrations of Mg, OH, and H3O+ in. the Mg(OH)2 system of Example 11-5. Solver finds the concentrations from the mass-balance expression, the solubility product of Mg(OEl)2, and the ion product of water. Then Excel s built-in tool Goal Seek is used to solve a cubic equation for the same system. The final exercise in Chapter 6 uses Solver to find the solubility of calcium oxalate at a known pH (see Example 11-7) and when the pH is unknown (see Feature 11-1). 

The techniques used to create this spreadsheet are shown in more detail in Appendix A, including (1) inserting an equation for calculation (2) inserting a text version of the equation for display (3) creating a border around a group of cells and (4) using Goal Seek. 

The first set of parameters used is 2/Vr = 8.7, (3 = 0.15, y = 30. The simple spreadsheet is shown in Figure 8.10 cells are set for T, which is calculated from the c. You can use either Goal Seek or Solver to find the value of c that makes the function zero. 

The power of the Excel program lies in its tools. Goal Seek and Solver were discussed Chapters 2-4, and Data Analysis is discussed in Appendix E. Other useful tools include Tools/Protection (to prevent part or all of the spreadsheet from being changed) and Tools/Flag for Follow Up (to notify you on some future date when you may need to update the spreadsheet). 

You can also use Goal Seek without first having made an elaborate spreadsheet table all that is needed is a blank spreadsheet and a single, analytical expression in terms of a single adjustable parameter. 

In this chapter we will encounter a number of standard mathematical operations that are conveniently performed and/or illustrated on a spreadsheet. We start with a brief description of the logic underlying the Goal Seek and Solver methods of Excel. Then we consider two methods often encountered in spectroscopy, viz. signal averaging and lock-in amplification. Subsequently the focus shifts toward numerical methods, such as peak fitting, integration, differentiation, and interpolation, some of which we have already encountered in one form or another in the context of least squares analysis and/or Fourier transformation. Finally we describe some matrix operations that are easy to perform with Excel. 

This should do it. Check it by switching to a spreadsheet, and selecting Tools. You should now see, between AutoCorrect and Goal Seek, the label Fourier transform, followed by an arrow pointing to the right. Click on Fourier transform, and the two choices will appear, Forward, and Inverse. Then verify that they indeed work. If not, try again you can Delete what does not work from the Menu Editor dialog box. 

Do the calculation by hand or use a spreadsheet. The Goal Seek command in Excel carries out the Newton-Raphson method automatically. See Chapter 3. 

H2. Show that the spreadsheet in Figures 2.B-3 and 2.B-4. has convergence difficulties if Goal Seek is used to make cell B19 (Ix = 1) equal 1 by changing cell B9 (V/F). 

We could also try not writing the Rachford-Rice terms and use Goal Seek to set the sum of in cell B19 = 1.0. In this problem. Goal Seek works for Zxi = 1.0 with V/Fgyesg >. 5 but does not work for low values of V/Fgu ss (See Problem 2.H2.1. This difficulty reinforces the need to check results from any software package, even one as common and robust as a spreadsheet tShacham et al.. 2008T... 

Using our previous answer, G = 0.360, as the first guess and using direct substitution, we obtain G = 0.404 as the answer in two trials. This equation is also easy to solve on a spreadsheet with Goal Seek. 

Solving these three equations simultaneously in a spreadsheet using Goal Seek, we obtain ( )d = 0.302 and since the ratio > 1, use (j)d = ( d,feed 0.167. 

The following values were generated with a spreadsheet, by guessing the next value for yp co2 (1 desired, use Solver or Goal Seek.)... 

With 0, Xjjj, a, a, Pp, Pp and M known, this is a quadratic equation in Xp. The equation can be solved by the quadratic formula, although this requires a fair amount of algebra. The equation can also be solved with an Excel spreadsheet using Goal Seek or Solver. 

This equation can conveniendy be solved for for any specified value of using the formula for solution of quadratic equations, or by using Goal Seek or Solver in a spreadsheet. Note that the effective equilibrium parameter in Eq. 118-431 is (K g Crj/Ct). Since the total concentration in the fluid can easily be changed, this effective equilibrium parameter can be changed. This behavior is illustrated in Exanple 18-8 and Figure 18-19. 

A reasonable way to solve this equation is by trial and error, as shown in the following table. You can create this table by hand or, even more easily, with a spreadsheet. In cell Al, enter a guess for x. In cell A2, enter the formula =A1 (2 A1 H-0.030) 2 . When you guess X correctly in cell Al, cell A2 will have the value 7.9 X 10 . Problem 6-24 gives an even better way to solve this problem with Excel Goal Seek. 

I ii Excel Goal Seek. Solve the equation /(F-x) = K by using Goal Seek described in Problem 6-24. Guess a value of JC in cell A4 and evaluate jc /(F - jc) in cell B4. Use Goal Seek to vary the value of x until jc /(F - jc) is equal to AT. Use your spreadsheet to check your answer to (a) of Ask Yourself Problem 8-F. 

The procedure for calculation of dew point temperature for any two-component mixture is to assume a trial value of temperature calculate both values of vapor pressure using the Antoine Equation for solvent and nitrogen and using the above equation, solve for the total pressure. When it equals the specified total pressure, the trial temperature is the dew point temperature. Spreadsheets have a "goal-seeking" function to automate this work. 

Students can view the simple spreadsheet used to calculate the rates at Appendix 11, with the iterative process undertaken using the goal seek function. 

H+], then the formulae for the each of the individual terms, using the Ks, the amounts added and the guessed value of [H" "], and then the sum of the terms. Then use the spreadsheet s equation solver (Goal Seek on Excell) to make that sum = 0 by changing the values of the guess of the final [H+]. Once you have this debugged, you can use subsequent columns to solve the rest of the equations. 

Table F.l shows the preliminary steps for this solution. From Figure 10.8 we see that this temperature and pressme correspond to a point on the vapor-liquid equilibrium curve for propane, so we should look for three solutions to Eq.F. 13. Table F.2 shows all of those. The procedure is to guess a value of z, and then numerically solve Eq. F.13 (using goal seek on a spreadsheet or any other suitable...

After hitting the OK button on the Goal Seek window, the spreadsheet changes... 

---