Reference circular

Excel has the capability to resolve the circular reference by an iteration procedure, but the program must be instructed to do invoke it. This instruction is communicated using the TOOLS-PREFERENCES-CALCULATION pull-down menu. In the CALCULATION window, the ITERATION box must be checked to enable the iteration process. Further there are two boxes that provide the opportunity to specify a maximum number of iterations and an convergence tolerance. 

C7 - C25 Use the lower-right-hand cursor to drag the formula in cell C7 down through cell C25. At this point EXCEL will complain about a circular reference. This is resolved by enabling iteration with the TOOLS PREFERENCES.CALCULATION window. Beyond checking the iteration box, there are options for the number of iterations and the precision of the convergence. 

Using the lower-right-hand cursor, drag cell C19 down the column to cell C47. At this point, EXCEL should report that it is unable to resolve circular references. The iteration could be immediately enabled using the TOOLS PREFERENCES.CALCU-LATION window. However, it may be a bit more convenient to delay enabling the iteration until after all the difference formulas are entered. In this case, click OK and proceed. 

Since the cells all depend on their neighbors, there is a circular reference situation. Use the TOOLS PREFERENCES.CALCULATION menu to enable iteration. Once the iteration begins, keep track of the inlet axial velocity. It must have a value of —1, but there is no specific constraint that forces this boundary value. Instead, the radial-pressure-curvature eigenvalue must be adjusted in such a way as to drive cell B18 to its correct value. This can be done simply by iteratively adjusting the value in cell B14. In principal,... 

The DNA molecules in human chromosomes are linear. However, electron microscopic and other studies have shown that intact DNA molecules from some other organisms are circular (Figure 5.18A). The term circular refers to the continuity of the DNA chains, not to their geometrical form. DNA molecules inside cells necessarily have a very compact shape. Note that the E. coli chromosome, fully extended, would be about 1000 times as long as the greatest diameter of the bacterium. 

When a formula refers to itself, either directly or indirectly, it creates a circular reference. If a circular reference occurs. Excel issues a "Cannot resolve circular references" message and displays a zero value in the cell. 

Usually circular references occur unintentionally, because the user incorrectly entered a cell reference in an equation. But occasionally a problem can be solved by intentionally creating a circular reference. 

The whole process can be set up to happen automatically by creating an intentional circular reference. The calculation is illustrated in Figure 10-11. The cells in rows 28 and 29 of Figure 10-6 were copied and pasted into rows 39-40 of Figure 10-11. Then the initial value 5 in cell B40 was replaced by the formula =G40. In this way the improved estimate of x was entered as the start value of the process. 

Figure 10-11. Calculation of a root of a function by the Newton-Raphson method (before creating intentional circular reference).

When you press ENTER, the "Cannot resolve circular references" message is displayed. To force Excel to evaluate the circular reference, using the results of the previous calculation cycle as start values for the next cycle, choose Options...from the Tools menu and choose the Calculation tab. Check the Iteration box and enter 0.000001 in the Maximum Change box. (The default settings are Maximum Iterations = 100 and Maximum Change =. 001.) When you press the OK button the circular reference will be evaluated. The results of the calculations are shown in Figure 10-12. 

Figure 10-12. Finding a root by the Newton-Raphson method and circular reference.

Another example of a problem that can be solved by intentional circular reference and iteration is the calculation of the pH of a solution of a weak acid. Combining the equations for mass balance and K, we obtain the following equation for the [H" "] of a weak acid of concentration C mol/L. 

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)). 

An equivalent circular reference, in which a cell refers to itself indirectly, is shown in Figure 10-14. Cell C5 contains the formula =SQRT( C 3 B5) and cell B5 contains the formula =A5-C5. 

Figure 10-13. Circular reference. Cell C5 refers to itself.

If the expression =SQRT( C 3 (A5-C5)) is typed into cell C5 in Figure 10-14, the "Cannot resolve circular references" message is displayed when you click on OK in the dialog box, a zero value is displayed in the ceU. 

Circular.xls shows how to use an intentional circular reference with the Newton-Raphson method to find the roots of a pol)momial. A "Circular Reference" error message will be displayed upon opening the workbook. 

The working capital is estimated as 7 weeks cash cost of production minus 2 weeks feedstock costs plus 1% of the fixed capital investment, as described in Section 6.2.2. Because the cash cost of production includes the interest payable on the working capital, this sets up a circular reference in the spreadsheet. The spreadsheet options must be adjusted to ensure that the calculation iterates to convergence. The converged result is 59.5 MM. Note that the value calculated is about f 0% greater than it would have been had we estimated the working capital as f5% of fixed capital investment. 

Relevant MCC Circulars referred to can be obtained from the Document Control Centre at the regulatory authority. (Most of the essenticd information has been included in this section)... 

Martindale. The Complete Drug Reference. The Pharmaceutical Press, London. MCC Circulars referred to in the chapter No. Title... 

Step 8 Turn on the iteration feature for the circular reference and the problem is solved. 

In this example, you have two iterations - one because of the circular reference due to the recycle streams and one because of the nonlinear Rachford-Rice equation. Some computer programs cannot handle both of these complications together. Neither Goal Seek nor Solver worked for this example, and you iterated the vapor fraction by hand. 

It is possible to program the finite difference method in Excel and use the Calculation feature to handle the circular reference. Turn off the iteration, prepare the spreadsheet, and then turn the calculation back on. Whether this converges depends upon the initial guess. 

---