Brother International PDP350CJ Owner's Manual - English - Page 187

Using Iteration and Circular References Occasionally, you may want to create a formula in which the calculation uses the result of the calculation. This is called a circular reference. Consider the example on the left where to calculate gross profit, you subtract expenses from income. If one of the expenses is based on a percentage of the profit Ċ for example, commission payments Ċ the formula has to rely on the outcome of the calculation. If you calculate this formula in B5 more than once, each successive calculation results in a smaller value. This is called iteration. Iteration is the process of recalculating a circular reference repeatedly, until the values converge, and the difference in the result of each successive calculation is no longer significant. The table below shows the results of a simple iterative calculation so that you can see when the recalculation converges. S1234567891 IT 0tE e Rp A T I O N 0111111111 057899999 B 0057368994 578426 57310B 5789 5B 573 357 5 0578999999 057368999 5 5784268 B573100 578943 5736 B578 4 57 5 1111111111 00000000001 00000000000 B 05 Note in the table above how the value in B3 approaches 200 and the value in B4 approaches 100. Spreadsheet stops the iteration when the results of a circular reference converge on a single value. If convergence does not happen within the number of recalculations you specify in the Calculations dialog box, Spreadsheet automatically stops the iteration. You can specify the limit for the convergence with Maximum Change in the dialog box. If you specify zero for the maximum change, iteration will not stop until it converges (or reaches the maximum number of iterations). Before using circular references, you must turn on iteration using the Calculation choice on the Options menu. If you enter a formula with a circular reference but have not specified that you want to use iteration, the result of the formula appears as #CIRC#. Spreadsheet 173