Convergence calculations of flowsheets with recycle streams using EXCEL iterations.
You can do recycle computations on EXCEL using EXCEL`s ability to resolve circular references. For this purpose, first you should set the EXCEL`s Calculation Options to allow calculations with circular references.
To do this, select OPTIONS from the TOOLS menu. Then, choose the CALCULATIONS dialog pane (see the image below). Check the ITERATIONS option to allow calculation with circular references. Modify the maximum number iterations (Maximum Iterations) and the convergence criterion (Maximum Change) according to your needs.
If you uncheck the AUTOMATIC option, then you will have to press the F9 key on your keyboard to start the iterative calculations.
Overview of Solving Circular Reference Formulas Using Iteration
The Microsoft Excel Solver solution process involves successive trials, or iterations. During each iteration, a new set of changing cell values is used to recalculate the worksheet, and the constraints and optimum cell values are examined. The process stops when a solution is found with acceptable precision, when no further progress is possible, or when the maximum time allowed or the maximum number of iterations is reached.
When a formula refers to its own cell, either directly or indirectly, it is called a circular reference. To solve such a formula, Microsoft Excel must use iteration. When you use iteration to calculate formulas, Microsoft Excel calculates each cell involved in the circular reference once using the results of the previous iteration.
Using the Default Settings for Iteration: Unless you change the default settings for iteration, Microsoft Excel stops calculating after 100 iterations or after all values in the model change by less than 0.001 between iterations.
Iteration and the Order of Calculation: Because Microsoft Excel calculates worksheets based on formula dependencies, the order of calculation is not by row or column. Therefore, do not build an iteration model that depends on the order of calculation.
Microsoft Excel Cannot Resolve Circular References: Microsoft Excel cannot resolve formulas with circular references using normal calculation. When you make a circular reference, Microsoft Excel displays a message, and the status bar indicates a cell involved in the circular reference. If the circular reference is accidental, you need to redesign the formulas or logic to break the circular reference. If you are using a circular reference intentionally, use the Options command on the Tools menu. Then on the Calculation tab, select the Iteration check box.
If Iteration Cannot Reach a Solution: During iteration, values usually move closer to the correct solution with each iteration. This is called convergence. Occasionally, however, values move farther away from the correct solution with each iteration. This is called divergence. If your equations diverge during iteration, try varying the initial values used in the equations by some constant amount. If you do get a convergent answer, you can multiply or divide all the values to return to your original initial values. Some sets of equations diverge because they have no solution.
Calculation Tab, Options Command (Tools Menu)Controls when and how formulas in open documents are calculated.
Automatic: Calculates all dependent formulas every time you make a change to a value, formula, or name. This is the default calculation setting.
Automatic Except Tables: Calculates all dependent formulas except data tables. You can calculate data tables by choosing the Calc Now button in this dialog box.
Manual: Calculates open workbooks only when you choose the Calc Now button. When you choose Manual calculation, the Recalculate Before Save check box is automatically selected.
Iteration: Limits iteration for goal seeking or for resolving circular references. Unless you specify otherwise, stops after 100 iterations or when all values change by less than 0.001. You can limit iteration by changing Maximum Iterations, Maximum Change, or both.
Calc Now: Calculates all open worksheets, including data tables, and updates all open chart documents. Shortcuts: CTRL+ = F9
Calc Sheet: Calculates only the active worksheet, and updates only the charts on the worksheet and open charts linked to the worksheet. Shortcut: SHIFT+F9
Update Remote References: Calculates formulas that include references to other applications. When cleared, formulas use the last value received from the other application.
Precision As Displayed: Changes stored values in cells from full precision (15 digits) to whatever format is displayed. The displayed values are then used for calculations.
Save External Link Values: Saves copies of values contained in an external document linked to your Microsoft Excel worksheet. If a worksheet with links to large ranges on an external document requires an unusually large amount of disk space or takes a very long time to open, clearing this check box can reduce the disk space and time needed to open the worksheet.